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

[pendle666]

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:

[pre]A B C D E
AB 04 56 78 Z
[/pre]

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

 

[Andrzejek]

Could you show your CONCATENATE formula?

(Assuming your data starts in row 2)
If I put this formula in cell F2: [tt]=CONCATENATE(A2, B2, C2, D2, E2)[/tt]
I get: [tt]AB045678Z[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[combo]

You need text in CONCATENATE arguments, formatting does nothing. Either set columns as text in one of text to column steps, or convert cells values to text: =CONCATENATE(TEXT(A2,"00"), TEXT(B2,"00),...

combo

 

[SkipVought]

Hi,

On the first report I used text to columns

And did you, in the Text to columns wizard, specify each Column Data Format as TEXT?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

Hello all,

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 : [pre]=CONCATENATE(A2, B2, C2, D2, E2)[/pre]

So I've tried Combo's advice to

[pre]=CONCATENATE(TEXT(G19,"00"),TEXT(H19,"00"), TEXT(I19,"00"),TEXT(J19,"00"),TEXT(K19,"00"))[/pre]

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

 

[SkipVought]

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

WRONG!!!

In STEP 3 of the Text to columns wizard you MUST, for each of the parsed members (in your example FIVE), select TEXT for Column data format.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

Why use text to columns at all? Just use SUBSTITUTE against the original security number to remove the spaces