Extracting Specific Values from a Text string

**kollur** · 08-04-2015, 02:05 PM

Hi there,
I'm trying to extract data from an Account code cell. Example of an account code in my data is as follows:
310410-01-1-000

Starting from left to right:
The first 6 characters are the basic account code ( In this example 310410)
The 2 characters to the right of the first Hyphen are (Location codes)- In this example it is 01
The single character to the right of the second Hyphen is the Department code, which is "1" in this example.

I'm trying to extract the Location code and department code in 2 separate columns. I have tried using the following formula:
=MID(C2,FIND("-",C2)+1,2) - Cell C2 refers to the cell holding the entire account code.

By doing so I'm able to extract the Location but not the Department.

Could some one please help me? If my explanation is insufficient and/or incomplete then I will try and explain myself better.

Thank you one and all for the help rendered.

Cheers,
SJ

**ChemistB** · 08-04-2015, 02:09 PM

Are they always the same length? How do they vary?
If they don't vary, then
=MID(A1, 8,2)
=MID(A1, 11,1)

**ChemistB** · 08-04-2015, 02:12 PM

If they do vary but second value is always 1 character and always to the right of second hyphen, then

=MID(C2, FIND("^", SUBSTITUTE(C2, "-","^",2))+1,1)

**nigelbloomy** · 08-04-2015, 02:12 PM

With the account in D3 this worked to get the location:

Please Login or Register  to view this content.

and this to get the department:

Please Login or Register  to view this content.

**kollur** · 08-04-2015, 02:15 PM

Hi ChemistB,
Yes the length always remains the same, and the solution was quick and simple. Much appreciated.
Thank you and have a great day!

Cheers,SJ

**kollur** · 08-04-2015, 02:16 PM

Thanks Nigelbloomy!
It worked..

Cheers,SJ

**MarvinP** · 08-04-2015, 02:22 PM

Hi kollur,

This is a trick I learned a few years ago. I call it "blowing up using spaces" and then trim to get the parts.

=TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",LEN($A2))),1+(COLUMNS($A:A)-1)*LEN($A2),LEN($A2)))

The above formula will replace all the dashes with a bunch of spaces. Then the Mid function grabs each piece and the trim knocks off the extra spaces. See the attached and how it works on your problem.