Extracting values from a string

Nick

Board Regular
Joined
Jul 11, 2002
Messages
70
I am using the formulas below to extract part of a string and was wondering if there is a more efficeint/shorter formulas I can use to to achieve my aim?

Basically, I want to extract three parts of a string from the likes of the following:

CE16OV-Civilian Employment; Y/Y% #L5
HOUST2F-Housing Starts: 2-4 Units; Level #L3

The formulas below assume the above full string sits in C4 of the sheet.

1) Indicator - this is indentified using the ; character and represents all characters before ";"

=LEFT(C4,FIND(";",C4,1)-1)

For the first instance this returns: CE16OV-Civilian Employment
For the second instance it returns: HOUST2F-Housing Starts: 2-4 Units

2) Form - this is the string between the chracters ; and #

=LEFT(MID(C4,FIND(";",C4)+2,LEN(C4)),SEARCH("#",MID(C4,FIND(";",C4)+2,LEN(C4)),1)-2)

For the first instance this returns: Y/Y%
For the second instance it returns: Level

3) Lag - this is indentified using the # character and represents all characters after #

=MID(C4,FIND("#",C4)+1,LEN(C4))

For the first instance this returns: L5
For the second instance it returns: L3

The formula for the form extract is very long and would be good if there is a way to at least shorten this one! Note that the formulas will be used both within the context of being entered directly into a spreadsheet as a formula AND also within VBA code in which case I would be looking to return the value from the given formula to cells so any changes need to ensure I can replicate in VBA!

Hope someone out there as some ideas.

Thanks

Nick
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Try this for the middle one:

Excel Workbook
CDE
4CE16OV-Civilian Employment; Y/Y% #L5CE16OV-Civilian EmploymentY/Y%
5HOUST2F-Housing Starts: 2-4 Units; Level #L3HOUST2F-Housing Starts: 2-4 UnitsLevel
Extrcat text
 
Upvote 0
If the spaces in the data are consistent, we could also eliminte the TRIM:

=REPLACE(LEFT(C4,FIND("#",C4)-2),1,LEN(D4)+2,"")
 
Upvote 0
Hi Peter,

Thanks for the suggestion.

I've tried both variants and also changed D4 to C4 but the formula returns a blank?

=TRIM(REPLACE(LEFT(C4,FIND("#",C4)-1),1,LEN(D4)+1,""))

Nick
 
Upvote 0
Hi,

Did you consider using Data - "Text to columns" to do this?

You can use both ; and # as delimiter at the same time.

As you talked about using in macro I think it might be a better sollution.
 
Upvote 0
I'm not sure text to columm would be suitable for what I am trying to do which the following code will make clear.

However even when trying to run this code (vba eqv for Indicator extraction) I get a runtime error 424 - Object required.

The following on its own works fine but not the full line of code. Thought it maybe an issue with teh find command so included it as a worksheet function but still does not work.

Any ideas what I've done wrong anyone?

Thanks, Nick

Cells(5, i) = Left(Cells(4, i), 2)

Sub Post_modelling_Variable_Split()
Sheets("Output").Select
Dim x, i As Integer
x = Range("B1").End(xlToRight).Column
For i = 2 To x
'Indicator name - assumes indicator is in row 4
'Cells(5, i) = Left(Cells(4, i), 2)

Cells(5, i) = Left(Cells(4, i), Worksheet.Function.Find(";", Cells(4, i), 1) - 1)

Next i
End Sub
 
Upvote 0
I would suggest you start the Macro recorder and then do a "Text to columns" using ; and # as delimiter. The code you get you can then modify to place the data exactly where you want e.t.c.

Having the macro to place formulas in the cells seems to me not the ideal solution.
 
Upvote 0
The problem with text to column is that under the current set up, I need the data split out accross rows and do not think there is an equivalent command to that.

Under the formula solution, the idea is to do the calculations in VBA and paste only the results, as opposed to the underlying formula which might become necessery if I can't get the code to work in it's current form, in which case I would agree, it would become quite messy!

Nick
 
Upvote 0
OK.

About the code, the only problem I see is last line where you insert the formula. Try instead:


Cells(5, i).FormulaR1C1 = "=LEFT(R[-3]C, FIND("";"",R[-3]C, 1) - 1)"
 
Upvote 0
Hi Peter,

Thanks for the suggestion.

I've tried both variants and also changed D4 to C4 but the formula returns a blank?

=TRIM(REPLACE(LEFT(C4,FIND("#",C4)-1),1,LEN(D4)+1,""))

Nick
The suggestion was designed to be used in conjunction with your existing formula for the first part extracting the text before the ;

I don't know your layout but with the original text in C4, I had your formula
=LEFT(C4,FIND(";",C4,1)-1)
in D4 and then the formula I posted you can see is in E4. My formula needs the D4 (or whatever cell your first formula was in as it uses that result to help extract the middle bit.

Here it is again with all formulas for the first row shown. I have included both my suggested formulas for the middle section, one in E4 and one in F4.

Excel Workbook
CDEFG
4CE16OV-Civilian Employment; Y/Y% #L5CE16OV-Civilian EmploymentY/Y%Y/Y%L5
5HOUST2F-Housing Starts: 2-4 Units; Level #L3HOUST2F-Housing Starts: 2-4 UnitsLevelLevelL3
Extrcat text
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,136
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top