Extracting a Number from the middle of a string

[gooniegirl180]

Hi everyone,

I just can't get my head around how to extract a product length from the middle of a string. Our product codes have a variable number of characters but the length always follows the "/" character. Not a problem, except that some codes have a length with up to 3 decimal places and there may or may not be more string characters after the length.

Examples, and expected results are:

PRODA/5 (want to return "5")
PRODABC/5.355 (want to return "5.355")
PRODABC/5.355SPEC (want to return "5.355")
PRODXY/7000 (want to return 7000)
PRODXY/7.2NOP (want to return "7.2")

and so on. No VBA please, needs to be a worksheet formula.

Thanks in advance for the help.

 

[admiral100]

Hi,

TRy this :

B1 =LOOKUP(9^9,0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/19)),ROW($2:$17)))

[TABLE="width: 210"]
<colgroup><col width="70" span="3" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]PRODA/5[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]PRODABC/5.355SPEC[/TD]
[TD="class: xl63"]5.355[/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]PRODABC/5.355[/TD]
[TD="class: xl63"]5.355[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]PRODXY/7000[/TD]
[TD="class: xl63"]7000[/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]PRODXY/7.2NOP[/TD]
[TD="class: xl63"]7.2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

TRy this :

B1 =LOOKUP(9^9,0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/19)),ROW($2:$17)))

We don't know what the OP's data can look like, but he should be made aware your formula is not robust as it will return incorrect results for data like this...

PRODXY/7NOV (or any other 3-letter month abbreviation following the number)

PRODABC/5.355E4A (Excel interprets the E surrounded by two numbers as marking a number is scientific notation)

 

[admiral100]

An array formula that handles the cases you have written..

Ctrl+ Shift + Enter NOT just Enter

B1 =LOOKUP(9^9,0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/19)),MIN(IFERROR(FIND({"A","D","E","F","M","N","O","S"},MID(A1,FIND("/",A1)+1,10)&1/19),999))-1))

[TABLE="width: 299"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PRODXY/7NOV[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PRODABC/5.355E4A[/TD]
[TD]5.355[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PRODA/5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]PRODABC/5.355SPEC[/TD]
[TD]5.355[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PRODABC/5.355[/TD]
[TD]5.355[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]PRODXY/7000[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]PRODXY/7.2NOP[/TD]
[TD]7.2[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

I forgot to add "J" for january, june and july ....

 

[gooniegirl180]

This isn't working for me, but it could be because I'm not understanding the formula. I also may have given you a bum steer by inadvertently including the "NOV" in the product code example. Here are some of my ACTUAL codes (instead of mock-up ones I tried to create for privacy reasons):

LP16848/12DG (want to return 12)
RHSCRGV1154220/6.1 (want to return 6.1)
RHSEGV5025/8KOTZ (want to return 8)
MGPE40/6.5I (want to return 6.5)
TRDSB51/6.1CAE (want to return 6.1)
RHSEGV4020/8GR450 (want to return 8)
EAG5050/6 (want to return 6)

My product codes can be up to 22 characters long and I have to apply this formula over 15,000-odd lines.

Hopefully this is clearer. Thanks

 

[admiral100]

Hi,

Ctrl+ Shift + Enter NOT just Enter

B1 =MID(A1,FIND("/",A1)+1,MATCH(64,CODE(MID(TRIM(REPLACE(A1,1,FIND("/",A1),"")),ROW(INDIRECT("1:"&LEN(TRIM(REPLACE(A1,1,FIND("/",A1),""))))),1)),1))



[TABLE="width: 228"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]LP16848/12ADG[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]RHSCRGV1154220/6.1[/TD]
[TD]6.1[/TD]
[/TR]
[TR]
[TD]RHSEGV5025/8KOTZ[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]MGPE40/6.5I[/TD]
[TD]6.5[/TD]
[/TR]
[TR]
[TD]TRDSB51/6.1CAE[/TD]
[TD]6.1[/TD]
[/TR]
[TR]
[TD]RHSEGV4020/8GR450[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]EAG5050/6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]PRODABC/5.35d[/TD]
[TD]5.35[/TD]
[/TR]
</tbody>[/TABLE]

 

[gooniegirl180]

Thanks admiral100. This works but only if I copy the array formula line by line so the reference "A1" updates to "A2". It doesn't work if I select the entire range and enter the array formula as the "A1" doesn't update. I attempted to modify the formula by changing each occurrence of "A1" to "A1:A8" but this didn't work. Given that I have to copy this formula over 15,000 lines, is there a way to modify the formula to apply across all rows?

 

[jtakw]

Hi,

If the formula supplied by admiral100 works for you, Don't change anything, to quickly copy the formula down Column A, with the formula already being in A1.

Method 1, if you may have Blank rows in between your data, And/Or, you want the formula to go Beyond your existing data range in case more data may be added:

1. Select the first cell with formula (i.e., A1), Right click, Copy
2. Hit F5, in the "Reference" box, type A2:A15000 (increase this range if you like)
3. Hold down Shift, click "OK"
4. Control V, the formula is now copied to the end of the range you specified in Step 2 above.

Method 2, if you have No Blank rows in your data set, and Only need to copy the formula to the End of your data set:

1. Select the 1st cell with formula
2. Double Left click on the Cell Handle (little square at the bottom right corner of selected cell)
the formula is now copied to the end of your existing data range.

 

[FlameRetired]

A bit on the long side but can be entered and filled down without Ctrl + Shift + Enter ... use just Enter.

=REPLACE(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",20)),20)),
MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},
TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",20)),20))&
{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"})),99,"")

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

PRODA/5​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

PRODABC/5.355​

[/td][td]

5.355​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

PRODABC/5.355SPEC​

[/td][td]

5.355​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

PRODXY/7000​

[/td][td]

7000​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

PRODXY/7.2NOP​

[/td][td]

7.2​

[/td][/tr]
[/table]

If you are not already aware of it you can copy/paste this in an unused area

=TRANSPOSE(CHAR(ROW(65:90)))

Then click in the formula bar an hit the F9 function key. It will reveal preselected:

{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}

Just copy/paste that into the formula if you decide to modify it. Saves a lot of typing! Then just delete the temporary cell.