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

[stgermainr]

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?

 

[StephenCrump]

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]

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]

Try:

	A	B	C	D	E
1	5pt 3rb 1st 1bl 1-2fg 3-4ft	1-2		s	fg
2	0pt 5rb 1as 3bl 1to 0-3fg	0-3		N	3
3	4pt 5rb 2bl 2-8fg	2-8
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=MID(A1,FIND(s,A1)-N,N)

Named Ranges
Name	Refers To	Cells
N	=Sheet1!$E$2	B1:B3
s	=Sheet1!$E$1	B1: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]

Try:

	A	B	C	D	E
1	5pt 3rb 1st 1bl 1-2fg 3-4ft	1-2		s	fg
2	0pt 5rb 1as 3bl 1to 0-3fg	0-3		N	3
3	4pt 5rb 2bl 2-8fg	2-8
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=MID(A1,FIND(s,A1)-N,N)

Named Ranges
Name	Refers To	Cells
N	=Sheet1!$E$2	B1:B3
s	=Sheet1!$E$1	B1: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]

You're welcome. Thanks for posting back.