Concatenate

richmcgill

New Member
Joined
Feb 4, 2019
Messages
38
Office Version
  1. 2016
Platform
  1. Windows
I have four cells of data and I need to keep the formatting for two of the cells. I also need a ; between the sets of data.
cell a2 is a number a 1 or 2
cell b2 is 012345678 (this is a SSN without the dash's in between the numbers) need to keep all nine digits even if the leading numbers are zeros.
cell c2 name of person
cell d2 0012345678 (loan number) need to keep all 10 digits even if the leading numbers are zeros.

Should look like this
2;012345678;Persons Name;0012345678

The SSN and Loan Number need to keep the format.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
HI what version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Ok, two options depending on whether B2 & D2 are text with leading 0, or are numbers formatted to show leading 0s
+Fluff v2.xlsm
ABCDE
1
21012345678Fluff00123456781;012345678;Fluff;0012345678
3112345678Fluff00123456781;012345678;Fluff;0012345678
4
Sheet2
Cell Formulas
RangeFormula
E2E2=A2&";"&B2&";"&C2&";"&D2
E3E3=A3&";"&TEXT(B3,"000000000")&";"&C3&";"&TEXT(D3,"0000000000")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,996
Messages
5,622,074
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top