I want to seperate below mentioned example type numbers to extract from string
54218/75155/5
54221/45655/6
54218/45655/4
54802/65858/1
65458/56325/9
I have above mentioned type numbers which are in beginning or in middle or at end of long text string which also contains blank spaces.
I need to extract above mentioned type numbers in separate cell and also want in trimmed format, without blank spaces
We need more than that. Aare they always 5 digits/5 digits/1 digit?
A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.
Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
... and are you still on Excel 2007? please update your profile with your current version.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
In the absence of a sample sheet, a guess:
=IFERROR(LOOKUP(1000,SEARCH(L$1:L$5,A2),L$1:L$5),"")
Yes, It's always 5 digits/5 digits/1 digit.
And I have Excel 2007.
Sample Sheet Attached
How aboutFormula:
Please Login or Register to view this content.
It's working well but in some cases I have two this type of backslash separated numbers, in this case If I want to return first number in one cell and another in second cell, what will be the formula for that
please supply a new sample workbook, showing that.
And can you make the extra text TRULY representative of the extra text in your real data.
Sample Sheet
Firstly, that is not very realistic data.
Secondly I have no idea how to do what you want in 2007.
Can you confirm that you are wanting this to work with Excel 2007????
Yes, I have Office 2007
Your sample does not look realistic. You said there was also a lot if text, but there is no text in your sample. So please provide realistic samples.
Are you OK with VBA solution. I can not help... but others may be able to help.
This is actual and realistic example. It is ok if someone provide VBA solution
OK. One attempt at a formula based solution that requires helper columns... It uses sequential SUBSTITUTE formulae. It is practical IF the number of possible unique numbers in each string is not too large.
How many valid results, maximum, are there for an individual, single string. This is set up for 3. Beyond 5 or so it will get awkward.
Does this help? happy to explain, if needed.
I need maximum two individual numbers in single string. So it's enough for me
So - you're happy... yes?
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks