Add a zero to a result in text format. 1

[Mizzness]

All.

A simple question that hopefully has a simple answer.

The data I bring in via a VLookup formula I have formatted to text. This is because the data it's referencing is in text form. However, I would like to have the 4 digit data have a zero in front of it.
e.g.: 1234 to be 01234

Thanx for any & all help.

 

[bluedragon2]

="0"&<your lookup formula>

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Mizzness]

Blue,

That returned ="0"&VLOOKUP(D2,'Reference data'!A:F,6,FALSE)
in the cell.

I've also been tinkering with LEN & LEFT but to no avail.

Any ideas ?

Thanx.

 

[SkipVought]

Change the Cell Format to GENERAL.

Edit and hit ENTER.

Skip,

_[red][/red]

 

[Mizzness]

Skip,

Excellent idea.
One caveat, I only need the zero in front of a four digit result. Besides creating another column & using the RIGHT function, anyway of incorporating this into the formula ="0"&Vlookup ?

Thanx.

 

[SkipVought]

[tt]
=right(0"&VLOOKUP(D2,'Reference data'!A:F,6,FALSE),5)
[/tt]


Skip,

_[red][/red]

 

[SkipVought]

oops...
[tt]
=right("0"&VLOOKUP(D2,'Reference data'!A:F,6,FALSE),5)
[/tt]


Skip,

_[red][/red]

 

[Mizzness]

Skip,

Genius !
A star for helping in the difficulty of simplicity.

 

[bluedragon2]

Wouldn't you want:

="0"&RIGHT(VLOOKUP(D2,'Reference data'!A:F,6,FALSE),4)

in case the lookup result was greater then 5 characters?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[macropod]

Hi all,

Why not use:
=VALUE((VLOOKUP(D2,'Reference data'!A:F,6,FALSE))
and format the cell as '0000'? This would pad any number of less than 4 digits with 0s, while allowing numbers with 5 or more digits to be fully expressed.

Cheers

[MS MVP - Word]

 

[Rofeu]

Or, if you want your result to be text (which seems likely if you want a zero infront of some 'numbers') use:

=TEXT(VLOOKUP(D2,'Reference data'!A:F,6,FALSE),"00000")

Cheers,

Roel

 

[MaxEd]

Just in case you have blank spaces in your data.

=TEXT(trim(VLOOKUP(D2,'Reference data'!A:F,6,FALSE)),"00000")

-Laughter works miracles.