I have a text that I am trying to separate into two columns but the text is so inconsistent that when I try to to spilt the cells I can't get the text breaks in the right spot from one cell to another.
The only constant between all the cells that I can see would be a way to detect where to insert the break is 5 spaces.
I have pasted an example of what I am trying to do. I want to separate the part number from the qty in stock.
Hope someone can help
chsdjkthgkvndjkfgklsdjgkld 412 In-stock
dfuisdfjsdklfj;sdiofuskldjfosdfjz 631 In-stock
sdkusilckjskldfuskldcjsdklguklsddfkljgsd-dgjjkdfhsd 114 In-stock
fhiasdjfklsdjfk dklgjudkldklgjdklfgj jkgkldfgjdkl+ 27 In-stock
cjbklig;lfkv dfgidl;gkdfl;gil flgiglglkdlfgk++dfhdfjk 5 In-stock
A formula in next column like this will extract the numbers out...
=LOOKUP(9.999999999E+307,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$30))))
where A1 contains the first text string.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi and welcome to the forum.
If you want to extract the number to another cell, use this Array formula.(C+S+E)TOGETHER. nOT JUST eNTER.
=SUM((MID(0&A1;LARGE(IF(ISNUMBER(MID(0&A1;ROW($1:$256);1)*1);ROW($1:$256);1);ROW($1:$256));1)*1)*10^ (ROW($1:$256)-1))
yOUR TEXT IN a1
Hope to helps you.
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
Hi and thanks.
The problem here is that many of the part numbers have numbers in their description. The only identifier i can see woudl be the 5 spaces in between the part number and the qauntity.
sorry for the typos..
both formulas above are not working.
ok, I figured it out. I used this formula. It extracts the quantity in stock and puts it in it's own column.
Can I delete this info from the original cell though?
=right(a1,15)
Melisa, take a look to the example. Works OK!
cTRL+sHIFT+eNTER Together. Not just Enter.
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
You could use
=-LOOKUP(2,-RIGHT(TRIM(SUBSTITUTE(A1,"In-stock","")),{1,2,3,4,5,6,7,8,9}))
if the text is always "In-stock" exactly.
Good luck.
In another column you can use formula like:
=SUBSTITUTE(A1,B1,"")
copied down
Then you can copy this column and edit|paste special>>Values over the original.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
In another column you can use formula like:
=SUBSTITUTE(A1,B1,"")
copied down
Then you can copy this column and edit|paste special>>Values over the original.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
This worked wonderfully - thanks.
But now how can I delete the qty in stock from the original cell so just the part number shows?
Try:
=LEFT(A1,MIN(FIND(0,SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},0)&0))-1)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Unfortunately that doesn't work because the part numbers are all different lengths.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks