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

stgermainr

New Member
Joined
Jan 20, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 20, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

stgermainr

New Member
Joined
Jan 20, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top