INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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:

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

Could you show your CONCATENATE formula?

(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

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

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

Hi,

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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 :
=CONCATENATE(A2, B2, C2, D2, E2) 

So I've tried Combo's advice to

=CONCATENATE(TEXT(G19,"00"),TEXT(H19,"00"), TEXT(I19,"00"),TEXT(J19,"00"),TEXT(K19,"00")) 

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

"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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close