I need to "section" some data out of a cell. What I have now is:
21 octobre 2011 FLIGHT AUGMENTATION COMPU PN : B397BAM0510 SN : 725
This is all in the first colum. The information that I need extracted is the part number PN : ######### and the SN :######.
Any ideas?
Thank you! Looking forward to learning a lot here in the future. Hopefully I'll input some ideas.
Last edited by vlady; 01-16-2014 at 07:36 PM.
Hi JetRich,
Select column, click data on the ribbon, click the text to columns, select delimited, select spaces, click finish.
This will break the data into columns for you, and I imagine you will then be able to concatenate the data into the format you require. Otherwise add a workbook.
BP
Last edited by Break_Point; 01-16-2014 at 07:49 PM. Reason: spelling
Break_Point
Welcome to the forum
Is the formatting always the same for the data in one column? Specifically, is there always a space, colon, and another space after "PN"?After that part, is the actual part number always a fixed number of characters? Is the SN also a fixed number of characters?
Assuming your data is in A1:
Formula:
In B1
=REPLACE(A1,1,FIND("PN",A1)-1,"")
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Just thought I'd add this as well:
For the PN: =MID(A1,FIND("PN",A1)+5,FIND("SN",A1)-1-(FIND("PN",A1)+4))
For the SN: =RIGHT(A1,LEN(A1)-FIND("SN",A1)-4)
Keep in mind, though, that if 'PN' or 'SN' happen to be in the actual name, then these formulas will not work. You would have to change "PN" to "PN : ", and likewise with "SN", and then change the number of characters necessary.
Yes, there is always a space, colon, and another space then the part number (which varies in characters and length) same thing with the serial number.Originally Posted by majosum
Welcome to the forum
Is the formatting always the same for the data in one column? Specifically, is there always a space, colon, and another space after "PN"?After that part, is the actual part number always a fixed number of characters? Is the SN also a fixed number of characters?
this worked, but returned the PN and SN in the same column. is there a way to separate them?
Look at my response above. Enter each formula in a different cell.
majosum, worked like a champ. Thank you!
No problem!
Please keep in mind my warning though. If you happen to have a PN or SN in the actual name, this formula won't work correctly. If this may be a problem, let me know and I can edit the formulas.
for PN
=TRIM(MID(A1,FIND("PN",A1),FIND("SN",A1)-FIND("PN",A1)))
for SN
=REPLACE(A1,1,FIND("SN",A1)-1,"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks