Extracting data from cell between text or just with text

[dgrissam]

I am needing to pull numbers from the middle of a cell. The problem is that the data is entered by many different people and none of them do it the same way. One cell could have “W/.5LWOP/2.75LS” another cell “N 2.5 LS 1.75 LC” another “N 5LS” yet another “N 4@99 1.5LC 2LS 2LU”. Some use spaces between other use "/" and some use a space. The numbers I need are preceding the LS in each cell (including the “.” If there is one)

I have been told to use RegEx in VBA, however i have little experience with VBA and no experience with RegEx.

I have been working on this for weeks any help would be greatly appreciated.

 

[jtakw]

Hi,

See how this works out for your data.

B1 formula extracts the number immediately before "LS" as is, result as Text,
C1 formula converts same result to Number in case for further math/comparison:

Book1
	A	B	C
1	W/.5LWOP/2.75LS	2.75	2.75
2	N 2.5 LS 1.75 LC	2.5	2.5
3	N 5LS	5	5
4	N 4@99 1.5LC 2LS 2LU	2	2
5	N 2.5 LS 1.75 LC	2.5	2.5
6	W/2.75LS.5LWOP	2.75	2.75
Sheet436

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("LS",A1)),"L"," L"),"/"," "))," ",REPT(" ",100)),200),100))
C1	=LEFT(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("LS",A1)),"L"," L"),"/"," "))," ",REPT(" ",100)),200),100)+0

 

[Rick Rothstein]

I think this formula will do what you want...

=LOOKUP(9E+99,--RIGHT(TRIM(LEFT(A1,FIND("LS",A1)-1)),ROW($1:$99)))

 

[dgrissam]

Hi,

See how this works out for your data.

B1 formula extracts the number immediately before "LS" as is, result as Text,
C1 formula converts same result to Number in case for further math/comparison:

	A	B	C
W/.5LWOP/2.75LS
N 2.5 LS 1.75 LC
N 5LS
N 4@99 1.5LC 2LS 2LU
N 2.5 LS 1.75 LC
W/2.75LS.5LWOP

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2.75[/TD]
[TD="align: right"]2.75[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2.5[/TD]
[TD="align: right"]2.5[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2.5[/TD]
[TD="align: right"]2.5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2.75[/TD]
[TD="align: right"]2.75[/TD]

</tbody>

Sheet436​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("LS",A1)),"L"," L"),"/"," "))," ",REPT(" ",100)),200),100))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=LEFT(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("LS",A1)),"L"," L"),"/"," "))," ",REPT(" ",100)),200),100)+0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you ... Thank you ... Thank you. Formula (C1) does exactly what i needed it to do. I should have posted in here weeks ago and could have saved some extra gray hairs. I never would have come up with that on my own. Thank you.

 

[dgrissam]

Thank You Rick ... I just plugged your formula into my live data and it works perfectly. Sorry I was so excited when I saw the first post (and it worked) I almost missed yours. Thank you ... thank you ... thank you ... i wish i would have posted in this forum long ago to ask for help. There are people out there with far more knowledge then me.

 

[jtakw]

You're welcome, welcome to the forum.