Convert numbers to a specific text string

francis

New member
Joined
Sep 30, 2011
Messages
11
Reaction score
0
Points
0
Hello,

I have a number that I need to convert to the following text string formats. I have successfully converted 45 to the following text string using the custom format:
Wait(45000);

But it does not work with the ones below:

Convert 0.05 to the following text string:
<ParameterString>0.05</ParameterString>

Convert 0 to the following text string:
<Time>0</Time>

These are codes for a instrument and the text must be in the exact format above. Can anyone help.

Regards,

Frank
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Frank, and welcome to the forum.

You stumbled on something that surprises me with "Wait". I can't find much other text that works in the text function.

At any rate, easy to do with Substitute. The <> characters work, so we can Substitute ParameterString> in for >, and the { character also works. We can then Substitute in the / for that as follows:

=SUBSTITUTE(SUBSTITUTE(TEXT(B6,"<>#.##<{>"),">","ParameterString>"),"{","/")

Likewise, for Time:

=SUBSTITUTE(SUBSTITUTE(TEXT(B6,"<>#.##<{>"),">","Time>"),"{","/")

Both assuem your data is in cell B6, so you'll need to correct that.

Hope that helps,
 

eferrero

New member
Joined
Mar 22, 2011
Messages
17
Reaction score
0
Points
0
Location
Brisbane
Website
www.edferrero.com
Hi Ken,
Its been a while since I visited here, so I'll start with this easy one :)
Nice use of SUBSTITUTE, but isn't it easier to use the TEXT function with format strings? Like,
=TEXT(B6,"""<ParameterString>""#0.00""</ParameterString>""")
=TEXT(B6,"""<Time>""#0""</Time>""")
 
Top