Formula to add space before and after a digit within a string
Hello, Gurus!
I have an unique requirement....
Adding a space before and after a digit in a string.
Let's assume I have something like a1243jjjajdj1.
I need it to be like: a 1 2 4 3 jjjajdj 1
I have done it using a macro it everything goes fine, but I can't seem to find any formula or combination in particular to make this work. Any suggestions?
Thanks, Sujit
this is one rather ugly way to accomplish it...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"1"," 1 "),"2"," 2 "),"3"," 3 "),"4"," 4 "),"5"," 5 "),"6"," 6 "),"7"," 7 "),"8"," 8 "),"9"," 9 "),"0"," 0 ")
EDIT, by the way, the digits you showed in your example were just 1, 2, 3 and 4, if there are some numbers that are not in your data you could remove and shorten that formula if for example there are no 8s or 9s, just remove one (or more) substitutes and the number you don't have.
Last edited by Sam Capricci; 07-26-2019 at 09:12 AM.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hi Sambo kid
Thank you. I tried to use substitute in various ways but failed- don't know why it didn't strike me to do this. Thank you for the help.
However, there's another complexity with my requirement as well.
If a text is:
xa5 xcef34x c4 1v
It needs to be converted to:
xa 5 xcef 34 x c 4 1 v
Meaning, if there's an alphabet and a succeeding number, the number will have to precede with a space, like xa5 becomes xa 5.
However, if there is xa34, it becomes xa 34- where 3 and 4 doesn't have to split. Any idea how this would work?
I was guessing to try regex using a macro- but I am not that much into macros to explore this. Any suggestions using formula or macro would be really appreciated.
Thanks again
Sujit
you are saying...
however, your example shows xcef34, not xa34.if there is xa34, it becomes xa 34- where 3 and 4 doesn't have to split
an exception could be written into the formula to find that specific combination of xa followed by some double digit number but is that the only exception or would there be other instances?
Unfortunately, there will be other instances.
xa or xcef are completely for representational purposes. The sole purpose is to get a macro/formula that could separate data like this...
I was just thinking to replace all 1-99 with " 1 " to " 99 ".
That would, however put a space in between 11 where there is a double digit number.
The next step would be to replace all double spaces with a single space.
And the next and last step would be to replace all "0 0"-"9 9" (all 2 digit combinations) with "00"-"99".
I am hoping this would work.
Any advice?
well in your first example you have 1234, if you tried to replace all 1 - 99 with "1 " to " 99 " excel wouldn't know whether 1234 should be replaced with 1 2 3 4 or 12 and/or 23 and or 34.
perhaps uploading a more complete sample of what you are dealing with AND the expected results might help better.
EDIT, that substitute formula will deal with the numbers based on the order it finds them so 1234 would be 1 2 3 4 and not 23 or 34 and I've been playing with it and I don't think it will handle the double digits effectively anyway.
Last edited by Sam Capricci; 07-26-2019 at 10:07 AM.
Test my UDF
Please Login or Register to view this content.
Best Regards,
Maras.
Maras_mak - you probably need to advise the OP how to install a UDF. Many people won't know how. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
@AliGW of course, if there are such needs.
Copy the code of UDF to the VBA normal module :
us Alt+F11 --> click on Insert>Module --> paste the code in the code window on the right hand side.
In a sheet, e.g. in B1 = AppSpace(a1)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks