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