converting text
Posted by steve morgan on February 13, 2002 2:06 PM
I have a string coming off of an old report that I need to reformat. Unfortunately I have a lot of trouble with this.
The string is coming off as 7 text digits, all starting with 0 (zero).
I need to reformat it as S0.xxx.xxx where 0xxxxxx is the old stock number.
Can anyone help me ?
Thanks to all
Steve Morgan
Wilmington North Carolina USA
Posted by Juan Pablo G. on February 13, 2002 2:14 PM
Try this formula if you have the text string (7 digits)
="S"&0&"."&MID(A2,2,3)&"."&MID(A2,5,3)
If you convert them to "real" numbers, use this function
=TEXT(A2,"\S0#\.###\.###")
Juan Pablo G.
Posted by Mark W. on February 13, 2002 2:59 PM
Make that =TEXT(A1,"\S0\.000\.000") ...
...and there's no need to convert your values
to "real" numbers... TEXT() expects a numeric
value as it's first argument and will coerce
a text value to a number if permissible.
Posted by Juan Pablo G. on February 13, 2002 3:21 PM
Mine should have come out as =TEXT(A2,"\S0\.###\.###") [NT]
Posted by steve morgan on February 13, 2002 5:37 PM
Re: Mine should have come out as =TEXT(A2,"\S0\.###\.###") [NT]
Thanks, Juan Pablo
It worked perfectly
Steve Morgan
Posted by Mark W. on February 14, 2002 10:48 AM
Your use of #s poses a problem...
Try it with "0012345" or "0000001". The #'s
prevent the display of all but the 1st leading
0. This problem is avoided with the use of
=TEXT(A2,"\S0\.000\.000"). Within a number
format #s are used to suppress insignificant
leading or trailing zeroes. Obviously, for
account numbers and the like there are no
insignificant digits.
Posted by Juan Pablo G. on February 14, 2002 12:52 PM
Re: Your use of #s poses a problem...
Thanks for that.
Juan Pablo G.
