This is my first post here. Any help would greatly appreciated. I am trying to separate alpha characters, followed by numeric characters, followed by more alpha characters in 3 separate columns. There are no delimiters, and each set of characters can be different lengths. The alpha characters are always capitalized. I would like to do this with formulas.
Example:
A1=1C105
A2=9MP028
A3=25MJR338
A4=8PV4545
Thank you in advance for your help.
Trevor
Would results look like this?
1c105 1 c 105 9mp028 9 mp 028 25mjr338 25 mjr 338 8pv4545 8 pv 4545
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
Yes, except the alpha characters would still be capital letters.
Using your posted examples and assuming no text will be longer than 10 characters
These formulas, copied down, return the individual segments:
Left digits
Right digits
B1: =LEFT(A1,LOOKUP(10^10,INDEX(--LEFT(A1,{1,2,3,4,5,6,7,8,9,10}),0),{1,2,3,4,5,6,7,8,9,10}))
Middle textD1: =RIGHT(A1,LOOKUP(99^99,--RIGHT($A1,{1,2,3,4,5,6,7,8,9,10}),{1,2,3,4,5,6,7,8,9,10}))
These are the results:C1: =MID(LEFT(A1,LEN(A1)-LEN(D1)),LEN(B1)+1,10)
Data Range
Is that something you can work with?
Last edited by Ron Coderre; 07-20-2015 at 03:57 PM.
But if you make the minutest of changes to the first example given by the OP, i.e. if A1 contains 1E105 instead of 1C105 then all these fail.Originally Posted by Ron Coderre
Left digits
Right digitsB1: =LEFT(A1,LOOKUP(10^10,INDEX(--LEFT(A1,{1,2,3,4,5,6,7,8,9,10}),0),{1,2,3,4,5,6,7,8,9,10}))
Middle textD1: =RIGHT(A1,LOOKUP(99^99,--RIGHT($A1,{1,2,3,4,5,6,7,8,9,10}),{1,2,3,4,5,6,7,8,9,10}))
C1: =MID(LEFT(A1,LEN(A1)-LEN(D1)),LEN(B1)+1,10)
And that's just one example. They also fail (given the appropriate date/language settings, that is) if A1 contains e.g. 1MAR105, etc., etc.
In B1:
=-LOOKUP(1,-(LEFT(A1,{1,2,3,4,5,6,7,8,9,10})&"**0"))
In D1:
=-LOOKUP(1,-(RIGHT(A1,{1,2,3,4,5,6,7,8,9,10})&"**0"))
should be more rigorous (and a touch simpler).
Regards
Worked like a charm. This was a huge help to me and I really appreciate it.
Thank you
Glad you got something you can use!
If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)
@Ron Coderre
Apologies. I see the need for your result_vector set-up now to deal with leading zeroes.
In that case:
=LEFT(A1,LOOKUP(1,-(LEFT(A1,{1,2,3,4,5,6,7,8,9,10})&"**0"),{1,2,3,4,5,6,7,8,9,10}))
and:
=RIGHT(A1,LOOKUP(1,-(RIGHT(A1,{1,2,3,4,5,6,7,8,9,10})&"**0"),{1,2,3,4,5,6,7,8,9,10}))
Nice idea by the way.
Regards
Thanks for the additional help XOR LX. I will mark this as solved.
I know this has been marked as solved...but, I just thought the solution could be simpler than I was making it.
Consequently, I came up with these formulas:
There might be shorter solutions, but I suspect they might be too arcane to readily understand.Left digits: B1: =LEFT(A1,MATCH(1,--ISERR(-MID(A1,{1,2,3,4,5,6,7,8,9},1)),0)-1) Right digits: D1: =RIGHT(A1,MATCH(1,--ISERR(-RIGHT(A1,{1,2,3,4,5,6,7,8,9})),0)-1) Middle letters: C1: =MID(LEFT(A1,LEN(A1)-LEN(D1)),LEN(B1)+1,10)
I have not yet finished my spreadsheet and I will check out your latest solution. Thanks again for your help with this. You probably saved me several hours.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Trevor K 
Register To Reply
Bookmarks