Data from 2 cells into one, with specific format

happydonut

Board Regular
Joined
Nov 28, 2019
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have this code to add the value from two cells into one;
VBA Code:
Range("C1").Value = Range("A1").Value & " " & Range("B1").Value

The cell in A1 has a date, e.g. 2021-01-25
The cell in B1 has a number, e.g. 123456789
Goal is to have the value in C1 as MMDD from cell A1 followed by the value in B1, e.g. 0125 123456789

Questions:
1. How do I format the C1 to get the value there as explained?
2. In real life, there could be many rows in A1 (all dates) and B1 (all numbers). How can I make a code that takes all rows into consideration, e.g. A2+B2=C2, A3+B3=C3 etc until last row with a value?

Thanks.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,594
Office Version
  1. 365
Platform
  1. Windows
Hi,

1. How do I format the C1 to get the value there as explained?

Range("C1").Value = FORMAT(Range("A1").Value,"MMDD") & " " & Range("B1").Value

2. In real life, there could be many rows in A1 (all dates) and B1 (all numbers). How can I make a code that takes all rows into consideration, e.g. A2+B2=C2, A3+B3=C3 etc until last row with a value?

You could try something like.

VBA Code:
Sub m()
Dim R As Range
Set R = Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
R.Value = Evaluate("TEXT(" & R.Offset(0, -2).Address & ",""MMDD "")&" & R.Offset(0, -1).Address)
End Sub
 

happydonut

Board Regular
Joined
Nov 28, 2019
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Hi,



Range("C1").Value = FORMAT(Range("A1").Value,"MMDD") & " " & Range("B1").Value



You could try something like.

VBA Code:
Sub m()
Dim R As Range
Set R = Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
R.Value = Evaluate("TEXT(" & R.Offset(0, -2).Address & ",""MMDD "")&" & R.Offset(0, -1).Address)
End Sub
Hi, and thanks for reply.

I just tested it. It works, but it jumps over the first row. So it starts with the second.
 

happydonut

Board Regular
Joined
Nov 28, 2019
Messages
57
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi, did you try changing the row reference in this part "...Range("C2:C" & Ran...." to a 1?
Ah, I did not. Thank you :)
 

happydonut

Board Regular
Joined
Nov 28, 2019
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Just a follow up question,

What if I want the value to be added in another workbook?

So for instance, Value in A1 and B1 from workbook 1
to value C1 in workbook 2
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,594
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This assumes that workbook 1 is the active workbook when the macro runs, if that cannot be guaranteed then some changes will need to be made to full qualify all of the ranges.

VBA Code:
Sub m()
Dim R As Range
Set R = Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
Workbooks("Book2").ActiveSheet.Range("A1").Resize(R.Rows.Count) = Evaluate("TEXT(" & R.Offset(0, -2).Address & ",""MMDD "")&" & R.Offset(0, -1).Address)
End Sub
 

happydonut

Board Regular
Joined
Nov 28, 2019
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Thank you.
Sorry to ask another question, but..

What if there is a filter in workbook 1 (i.e. not all dates visible). How can the macro only take the filtered data from Workbook 1 to Workbook 2?
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,594
Office Version
  1. 365
Platform
  1. Windows
Hi, it would need a different technique altogether - you'd probably need to loop through the visible cells one by one.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,723
Office Version
  1. 2010
Platform
  1. Windows
I have this code to add the value from two cells into one;
VBA Code:
Range("C1").Value = Range("A1").Value & " " & Range("B1").Value
Hi, just obviously use Text rather than Value for the source part, not for the destination …​
 

Forum statistics

Threads
1,147,560
Messages
5,741,825
Members
423,689
Latest member
Jords998

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