[Solved] merging 2 rows in one column

[satyak]

how to add 2 rows in to one columns specillay nos

i have the data like this

45890123 - this no in 1 st row
3 - this no in 2nd row

i want out put like this - 458901233 in one row

 

[Seemanto Saha]

Dear satyak,

Thanks for sharing your problem with us. I understand that you want to combine cell values from two rows to one cell. As the data in your provided sample contained numerical values, I can show you two methods to merge the required values. Here, I will use the following dataset-​

Combining as text:

Select Cell B3 >> insert the following formula >> press the Enter key >> drag the Fill Handle icon to the right to copy the formula in the remaining cells.​

=CONCATENATE(B1,B2)

Other than the CONCATENATE function, we can also use the CONCAT and TEXTJOIN functions.

Combining as numbers:

Select Cell B3 >> insert the following formula >> press the Enter key >> drag the Fill Handle icon to the right to copy the formula in the remaining cells.​

=B1*10+B2

​

Use these methods according to your requirement. Let us know your feedback. The workbook used for solving this problem is attached below.​

Regards,
Seemanto Saha
ExcelDemy

 

[mathi]

I also have same problem. In one sheet I have numbers from 0 to 15 in column as number but i need to copy that data in other sheet regularly and in other sheet that column is as text because i need leading 0 from 0 to 9 and i am doing it manually,
Please tell me is it possible in any way to get leading zero in text column like 00 , 01, 02 instead of 0, 1, 2 on pasting that data.
I tried some methods but every time when i paste that data so it doesn't add that leading zero.

 

[Seemanto Saha]

Hello mathi,

Welcome to ExcelDemy Forum and thanks for sharing your problem with us. I understand that you want to add leading zeroes for 0 to 9 while copying the numbers 0 to 15 from another sheet. We can accomplish this using the TEXT function.​

Here, I have taken the numbers 0 to 15 in a worksheet named “Numbers”.

​

To copy these numbers to another sheet with the required text formatting, I used the following formula in Cell A2, pressed the Enter key, and dragged down the Fill Handle icon.​

=TEXT(Numbers!A2,"00")

​

Here, the “Numbers!” part in the formula refers to the sheet from where the numbers are copied. Replace it with the name of your required worksheet.

I hope this resolves your problem. Let us know your feedback.

Regards,
Seemanto Saha
ExcelDemy