It appears to me that you are starting with a 21 character text string, and you are trying to manipulate the text string into a space delimited text string with the 21 characters separated into "groups" with spaces between. The presence/absence of the non-numeric character in the text string does not seem to change the desired spacing/grouping. Assuming those observations are correct, I would expect some simple text manipulation functions would be able to do this:
1) Left(text,2) will get the first two characters.
2) Mid(text,3,3) will get the next three characters.
3) Mid(text,6,3) will get the next three characters. (and so on with Mid() functions to extract the different "groups")
4) Right(text,3) will get the last three characters.
5) Join the individual text strings together with a space between each "group." In VBA, if you store the above "groups" into an array, then you can use the Join() function https://learn.microsoft.com/en-us/of.../join-function Otherwise, just use the concatenate operator to combine the individual text strings together
In VBA, this might look like:
Will something like that work for you, or are you required to use "formatting" (like a Format function or Excel number formatting)?
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by shg
Bookmarks