• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula on one sheet to select and return text between two specific points in a string on another sheet

B

belinea2010

New Member
I have a several thousand complicated strings in column A on a work sheet named "Data_Import" so cell 1 would be Data_Import!A1 and so on.

On another work sheet named "Pack" I need a formula in Column G cell G1 (going downwards) that can extract and return whatever text exists between the ")" character and the word "kHz" in the string and remove the underscores "_" and replace them with spaces " ".

The strings all have the same layout which is:
4_JnI_Bhhuhgdl_-_jjkdj'h1_62_[Hmmd_175H297j]_(5_T27keA)_127_kHz_Je_87A_zzE
24_JnI_stgvuhgdl_-_jjkdj'h1_62_[12_175_7j]_(A_T27keA)_48_kHz_14_2f3_TQe

The numbers before kHz will always be either 2 characters or 3 characters in length followed by a space.

The data in each string will be different and of varying lengths but there are some constants which will not change.

Using my dummy examples above the constants are "_-_" , "[" , "]" , "(" , ")" and "kHz".

Again using my dummy examples the data I need to extract is
"127_kHz" and then the formula would remove and replace the "_" and return them as "127 kHz"
"48_kHz" and then the formula would remove and replace the "_" and return them as "48 kHz"

This is beyond my limited knowledge so I need some help?
 
S. Das said:
Yes, but belinea already mentioned that always the digit will be 2 or 3.
Click to expand...

You are right, the the OP only requested for 2-3 digits.

But, in remind that other reader may not notice to this.

It will be pleased to give some "note" or "remark" and warning the readers regarding your posted formula limitation.

Regards
Bosco
 
bosco_yip said:
You are right, the the OP only requested for 2-3 digits.

But, in remind that other reader may not notice to this.

It will be pleased to give some "note" or "remark" and warning the readers regarding your posted formula limitation.

Regards
Bosco
Click to expand...
Thank you for your advice bosco. I will keep it in mind from next time.
 
Thank you everyone you have solved my issue and I am very grateful.

I used bosco_yip's suggest but I want to thank every who tried to help :)
 
There will be other ways:
68811
This relies on the new Office 365 LET function to allow the formula to be broken down into named steps.
The formula is entered into cell D3 and spills to return the complete column.
It looks more like programming than a spreadsheet formula ;). The same thing can also be done using named formulas rather than the more local names in LET but that involves working with Name Manager and the count of Names tends to proliferate rapidly.
 
Last edited:
An even more obscure approach is to use Charles Williams's (a longstanding MVP) FastExcel library of functions. I am primarily interested in the dynamic array functions such as ACCUMULATE, SUMROWS, VSTACK etc. but there are also a number of functions based on regular expressions (RegEx) including Rgx.FIND and Rgx.MID

The formula
= Rgx.MID( strings, "(\d+)(?=_kHz)", 1 ) & " kHz"
returns digits (an arbitrary number of consecutive digits) that are followed by the literal text "_kHz".

Note: The "\" is an escape character that causes "d" to return digits rather than the literal character; "+" matches one or more digits; "?=" looks ahead and only returns a digit group that is followed by "_kHz".
References: https://regexr.com/
http://www.decisionmodels.com/FastExcelV4.htm
 
Last edited:
Hi Peter.

Thank you for your kind suggestion and explanation.

I am only using Excel 2007 and as of today Excel 2016 and my knowledge of formulas for those excel versions is limited but I am learning especially from the kind posts and suggestions of good people on forums and work colleagues.
 
@belinea2010 I probably owe you an apology. I am interested in modern dynamic arrays and methods that do not involve using direct cell referencing; defined names only. The solution I have presented has served to demonstrate that Excel solutions do not have to look like traditional end user computing; that said they are of no value to you without Office 365! You might like to check out whether this last construction works in Office 2007. As I remember, that was the point at which Tables appeared in Excel but there may be other pitfalls I have forgotten.

68836
 

Attachments

  • Compound Strings.xlsx
    13.3 KB · Views: 4
Hi Peter.

You do not owe me any kind of apology as I am grateful that anyone takes time to reply
and especially a reply that shows me new ways of doing that which I am trying to achieve.

Formulas and VBA are all fairly new to me and I learn enormously from the solutions the kind members of this forum have suggested.

I have download the worksheet that you attached to your post and I will have a look at your solution as I am very keen to become more proficient so thank you, I appreciate it.
 
@belinea2010

I have recorded a video showing a number of solution development practices applied to your problem of extracting data from strings. The first approaches draw from the solution put forward by @bosco_yip but anchors the search on the "_kHz" fragment rather than the less specific ")_" that precedes the frequency. The final approaches rely on functionality only available within Excel on beta release and on a commercially offered addin from Charles Williams, so they demonstrate a possible future rather than being a practical solution for today.

 
You must log in or register to reply here.
Back
Top