## Excel 2013 - using concatenate with numbers and leading 0's

## Excel 2013 - using concatenate with numbers and leading 0's

(OP)

Hello

Hope the title makes sense!

I've got a report of people and their social security numbers - it's been written like this: AB 04 56 78 Z

I need to do lookups against another report which has the number written in it's usual form: AB045678Z

On the first report I used text to columns so that each pair of characters and it's final letter are in separate columns:

Then used CONCATENATE to bring them back together again. Now I formatted the split columns to make sure that there were always two characters. But after concatenate the 0 disappears, so I end up with AB45678Z. Unfortunately there are 250 records from 865 which are affected, so I don't really want to check them manually! It's a report that will need to be done within a tight timeframe each month, so I'd like to sort this and perhaps make it a macro.

Hope the title makes sense!

I've got a report of people and their social security numbers - it's been written like this: AB 04 56 78 Z

I need to do lookups against another report which has the number written in it's usual form: AB045678Z

On the first report I used text to columns so that each pair of characters and it's final letter are in separate columns:

A B C D E AB 04 56 78 Z

Then used CONCATENATE to bring them back together again. Now I formatted the split columns to make sure that there were always two characters. But after concatenate the 0 disappears, so I end up with AB45678Z. Unfortunately there are 250 records from 865 which are affected, so I don't really want to check them manually! It's a report that will need to be done within a tight timeframe each month, so I'd like to sort this and perhaps make it a macro.

thank you for helping

____________

Pendle

## RE: Excel 2013 - using concatenate with numbers and leading 0's

(Assuming your data starts in row 2)

If I put this formula in cell F2: =CONCATENATE(A2, B2, C2, D2, E2)

I get: AB045678Z

---- Andy

There is a great need for a sarcasm font.## RE: Excel 2013 - using concatenate with numbers and leading 0's

combo

## RE: Excel 2013 - using concatenate with numbers and leading 0's

And did you, in the

Text to columnswizard, specify eachColumn Data FormatasTEXT?Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel 2013 - using concatenate with numbers and leading 0's

Thank you for your replies.

Here's what I did:

next to the NINO column I added in 4 new columns and formatted as text.

Using text to columns, I made sure I selected text, but the 04 came out as a 4

My original concatenate formula was as :

So I've tried Combo's advice to

And that has worked. G19 being one of the rows with the offending 0.

So that looks like my solution. I've only ever used concatenate to join words together, not numbers.

thank you

thank you for helping

____________

Pendle

## RE: Excel 2013 - using concatenate with numbers and leading 0's

WRONG!!!

In

STEP 3of theText to columnswizard you MUST, foreachof the parsed members (in your exampleFIVE), selectTEXTfor.Column data formatSkip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel 2013 - using concatenate with numbers and leading 0's