Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Combine cells with format

0

Hi, this may be super easy for somebody on here.

I have 3x columns I want to combine to form a NI number

AB23   09   36C  the result I want is AB230936C, basically combine what I am seeing in each cell.

However when I use the concatenate function, the result is AB23936C. The leading zero in my custom format for 2 digit numbers in the middle cell is ignored.

e.g.

AB23   09   36C   AB23936C

AB27   04   54C   AB27454C

Why is it so hard to combine cells?

Answer
Discuss

Answers

0
Selected Answer

If you upload your exact example, it would help....it's difficult to see what you are doing.  Especially how the 09 becomes a number 9.  In any event, you may need to format the alleged number with preceding zero.  If you're in cell F1 AND you assume that the 4 other text combinations are in cells A1 through D1 - here is the formula 

=+A1&TEXT(B1,"00")&C1&D1

NOTE: you may have to adapt this to your particular situation, but you should be able to adopt it to your needs.

Discuss

Discussion

Thanks, I didn't know how to upload so I typed an example with the spaces to represent columns.

Thank you for your help!
c50 (rep: 4) Apr 18, '19 at 8:53 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login