# How can I extract a certain number of characters before a particular string or character?

#### stgermainr

##### New Member
So I have data in a column that is delimited by spaces. It would be simple to delimit by the spaces to get the desired result but the data is not as you can see below:

 5pt 3rb 1st 1bl 1-2fg 3-4ft 0pt 5rb 1as 3bl 1to 0-3fg 4pt 5rb 2bl 2-8fg

If I delimited by space, I would have st, as, and bl in the 3rd column instead of just having each category in their own column.

My solution is to create a formula to extract x number of characters before a particular string. Example:

Extract first character before the string "rb", or extract the first 3 characters before the string "fg" or extract the the first 2 characters before "pt" .

What would be the formula to do this?

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### StephenCrump

##### MrExcel MVP
Welcome to the Forum!

It's not clear what output you'd like to see for the example provided. Can you please post more detail, thanks.

#### stgermainr

##### New Member
Welcome to the Forum!

It's not clear what output you'd like to see for the example provided. Can you please post more detail, thanks.
Yes, sorry:

So, the 3 cells are as follows in A1, A2 and A3:

 5pt 3rb 1st 1bl 1-2fg 3-4ft 0pt 5rb 1as 3bl 1to 0-3fg 4pt 5rb 2bl 2-8fg

In cell B1, I would like to see the first 3 and only the first 3 characters that appear before the string "fg". So that would return: 1-2 in cell B1

If I copy the formula down, cell B2 would return: 0-3

and cell B3 would return: 2-8

#### StephenCrump

##### MrExcel MVP
Try:

ABCDE
15pt 3rb 1st 1bl 1-2fg 3-4ft1-2sfg
20pt 5rb 1as 3bl 1to 0-3fg0-3N3
34pt 5rb 2bl 2-8fg2-8
Sheet1
Cell Formulas
RangeFormula
B1:B3B1=MID(A1,FIND(s,A1)-N,N)
Named Ranges
NameRefers ToCells
N=Sheet1!\$E\$2B1:B3
s=Sheet1!\$E\$1B1:B3

This will work for the small sample you've posted. You should check it works OK for larger samples, e.g. it won't work on: 4fg 5rb 2bl 2-8fg

Note that FIND() is case sensitive. You can use SEARCH() instead if you want non-case sensitive.

#### stgermainr

##### New Member
Try:

ABCDE
15pt 3rb 1st 1bl 1-2fg 3-4ft1-2sfg
20pt 5rb 1as 3bl 1to 0-3fg0-3N3
34pt 5rb 2bl 2-8fg2-8
Sheet1
Cell Formulas
RangeFormula
B1:B3B1=MID(A1,FIND(s,A1)-N,N)
Named Ranges
NameRefers ToCells
N=Sheet1!\$E\$2B1:B3
s=Sheet1!\$E\$1B1:B3

This will work for the small sample you've posted. You should check it works OK for larger samples, e.g. it won't work on: 4fg 5rb 2bl 2-8fg

Note that FIND() is case sensitive. You can use SEARCH() instead if you want non-case sensitive.
That worked perfectly, thank you for you prompt assistance!

#### StephenCrump

##### MrExcel MVP
You're welcome. Thanks for posting back.

1,129,277
Messages
5,635,252
Members
416,849
Latest member
truerock

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