# Extracting values from a string

#### Nick

##### Board Regular
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

If the spaces in the data are consistent, we could also eliminte the TRIM:

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

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

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.

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

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.

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

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)"

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 ;

=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

Replies
7
Views
491
Replies
19
Views
446
Replies
14
Views
725
Replies
6
Views
577
Replies
5
Views
140

1,203,139
Messages
6,053,724
Members
444,681
Latest member

### 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.

### Which adblocker are you using?

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

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