Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Extract Only A Certain Part Of The Contents In A Cell

Posted by: petite39 Oct 15 2019, 10:33 AM

The part number I need is buried in a cell. In the following example I need to grab the value after the wording "Part NO. " and then the next value is the part number and then a space after the part number. All the rest of the line after the part number space needs to be gone as well. For instance the first line I need only 131705014 in a separate cell.

Part No. 131705014 HEX BOLT M5 X 14 TMM140A
Part No. 912303502 (E) WOODRUFF KEY
Part No. 11 MAIN FRAME ASSY WC7
Part No. 23 CABLE GUIDE
Part No. 58287 MOMENTARY ON/OFF SWITCH INSERT
Part No. 912303701 (E) SCREW
Part No. 131905012 HEX SOCKET HD BUT. SCREW M5X12 TMM140

I know how to concatenate, but I don't know how to unconcatenate.

Posted by: arnelgp Oct 15 2019, 11:07 AM

on a new Cell, add this formula and replace A2 with the cell you want to extract:

CODE
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A2,"Part No: ","")," ", "                                                                                         "), 20))

Posted by: Larry Larsen Oct 15 2019, 11:09 AM

Hi
I'm no expert but just had a play, hope this will help:

CODE
=TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))), (B4-1)*LEN(A4)+1, LEN(A4)))


B4 = the 3rd word from the text string..


You can hard code the reference to B4 as 3

=TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))), (3-1)*LEN(A4)+1, LEN(A4)))

HTH's
thumbup.gif