UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Extract Only A Certain Part Of The Contents In A Cell, Office 2013    
 
   
petite39
post Oct 15 2019, 10:33 AM
Post#1



Posts: 1,220
Joined: 7-April 04



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.
Go to the top of the page
 
arnelgp
post Oct 15 2019, 11:07 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


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))

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Larry Larsen
post Oct 15 2019, 11:09 AM
Post#3


UA Editor + Utterly Certified
Posts: 24,385
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


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
Attached File(s)
Attached File  2019_10_15_17_06_51.jpg ( 36.1K )Number of downloads: 0
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2019 - 10:56 AM