extract number from multiple occurrence delimiter of string excel
I'm trying to extract all the numbers from the left of the delimiter ":" . They can occur once or more.
E.g. cell F2 contains BP2.2.1:40 BP2.2.1:50 BP2.2.1:60 BP2.2.1:70 BP2.4.1:80
and what i want as a result is : BP2.2.1 BP2.2.1 BP2.2.1 BP2.2.1 BP2.4.1
I've tried =IFERROR(LEFT(F2,FIND(":",F2)-2), " ") but only displays 1 out of a possible 4 in the cell.
Any suggestions?
Hi Manny339,
Welcome to the Forum.
Assuming your string is in A1, then try this......
In B1and copy across (right) until you get blank cells.
=IFERROR(MID($A1,FIND("*",SUBSTITUTE(" "&$A1," ","*",COLUMNS($B1:B1))),FIND("^",SUBSTITUTE(" "&$A1,":","^",COLUMNS($B1:B1)))-FIND("*",SUBSTITUTE(" "&$A1," ","*",COLUMNS($B1:B1)))-1),"")
Is this what you are trying to achieve?
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Originally Posted by sktneer
Hi Manny339,
Welcome to the Forum.
Assuming your string is in A1, then try this......
In B1and copy across (right) until you get blank cells.=IFERROR(MID($A1,FIND("*",SUBSTITUTE(" "&$A1," ","*",COLUMNS($B1:B1))),FIND("^",SUBSTITUTE(" "&$A1,":","^",COLUMNS($B1:B1)))-FIND("*",SUBSTITUTE(" "&$A1," ","*",COLUMNS($B1:B1)))-1),"")
Is this what you are trying to achieve?
Hi, thanks for the reply.
You solution only displays one result instead of the multiple strings in the cell with ":" as a delimiter.
What I am trying to achieve is getiing everything from the left of the delimiter ":" with multiple occurances
e.g. cell A1 = BP2.2.1:40 BP2.2.5:50
result cell B1 =BP2.2.1 BP2.2.5
Assuming your string is in F2: Try this formula in G2 and pull it to the right until you see blank cell
Press Ctrl+F and type in :?? and click Replace. In Replace with press bar space once and Replace All
=TRIM(MID(SUBSTITUTE(TRIM($F2)," ",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))
***Corrected formula
Last edited by AlKey; 08-14-2014 at 10:03 AM.
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
Sorry I'm a bit confused, i'm new to excel, the result should stay in the same cell.
E.g. A1 = BP2.2.1:40 BP2.2.5:50 (all in one cell)
result cell B1 =BP2.2.1 BP2.2.5 (all in one cell)
and i want to repeat this for all the other cells.
E.g. A2 = BP2.3.1:40 BP2.3.5:50 ( all in same cell)
B2 = BP2.3.1 BP2.3.5 (all in same cell)
One tweak to Alkey's formula, to return only the BP strings... again with the source data in f2...
Formula:
TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE($F2,":"," "))," ",REPT(" ",255)),(COLUMNS($A:A)*2-2)*255+1,255))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
For that probably you will need a VBA solution to achieve this. Please find the attached workbook with two sheets Test1 and Test2. Both these sheets contain a Green Button, click this button to get the desired output. See if this is something you can work with.
Test sheet 1 is what i want to achieve, but is there any other way to do it without using VBA because I don't know how you got that green button there and where the code is beneath it
@Today, 10:06 AM #5
Manny339
LOL, I think you effectively confused all of usIf you if you simply need to get rid of :xx part of the string and keep everything in one cell you can use just this
Press Ctrl+F and type in :?? and click Replace. In Replace with press bar space once and Replace All
No need for formula.
Not sure what you mean@Today, 10:06 AM #5
Manny339
LOL, I think you effectively confused all of us
Press Ctrl+F and type in :?? and click Replace. In Replace with press bar space once and Replace All
No need for formula.
I did this this and nothing happened
Last edited by Manny339; 08-14-2014 at 11:06 AM.
See if you can go with Alkey's suggestion. That is pretty simple and straight forward.
But if you want to continue with the VBA solution, there are two codes which are assigned to two green buttons on Test1 and Test2 sheets. To see the codes, follow these steps.
Press Alt+F11 to open VBA editor --> On project pane on the left side of VBA editor --> You will find two modules "Module1" and "Module2" --> Double click on each module to see the code.
The name of the code is pretty straight forward. The name itself will tell you what this code is doing.
You can delete the green button from the sheets and whenever you want to execute the code, press Alt+F8 to open Macro window and select the correct macro from the macro list and click Run.
Does that help?
Ok, I will try this again.
Copy and paste the entire column from the original location to a new column, select all rows in this column then follow steps bellow
Press Ctrl+F and type in :?? and click Replace button. In Replace with press bar space once and Replace All.
Use Ctrl H instead of F
Excel Guru in the making
<----------If the question has been answered to your satisfication please click the Add Repuation star to the left
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks