How to get the date and insert as text in the cell

Slavio

New Member
Joined
Mar 28, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi everybody. I have a formula: =SUBSTITUTE(SUBSTITUTE(B4;"No";"Nein");"Yes";"Ja")
in the cell but I can't figure out how to get the date and insert as text in the cell.
In cell B4, the date is in the following format: dd.mm.yyyy
In cell C4 must be like text because then, we copy this data via VBA into word.

The result in the cell should be the date inserted as text 06.05.2021

I have already searched the forum :)
Will you help?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Slavio

New Member
Joined
Mar 28, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hooray yabadabadoo I found it:
This article helped me

=TEXT(SUBSTITUTE(SUBSTITUTE(B3;"No";"Nein");"Yes";"Ja");"dd.mm.yyyy")
But it will write it in the word as: 44322
Will you help?
 
Last edited:

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
On the VBA side you can force it to regard the referenced cell as text, otherwise it'll regard it as the datevalue or something weird. I think it's FORMAT ... can use this on the spreadsheet too.

Easier to deal with on the excel side by merely adding a single quote mark ' before it... Insert into C4

= "'" & B4 (notice single quote between the double quotes)

... which forces the cell to display as text (will get the little green corner hash in the cell)

Then in VBA, pick it up as text using, to be pasted into word as the value of URDATE

Dim URDATE as String
URDATE = Sheets("Your Sheet").Range("C4").Value ' whatever the date cell is.... the VBA sees this as a text
 
Solution

Slavio

New Member
Joined
Mar 28, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
  2. Web
On the VBA side you can force it to regard the referenced cell as text, otherwise it'll regard it as the datevalue or something weird. I think it's FORMAT ... can use this on the spreadsheet too.

Easier to deal with on the excel side by merely adding a single quote mark ' before it... Insert into C4

= "'" & B4 (notice single quote between the double quotes)

... which forces the cell to display as text (will get the little green corner hash in the cell)

Then in VBA, pick it up as text using, to be pasted into word as the value of URDATE

Dim URDATE as String
URDATE = Sheets("Your Sheet").Range("C4").Value ' whatever the date cell is.... the VBA sees this as a text
Thank you for your response. I did it according to your instructions. It worked exactly as you said.
You led me on the right path :)
In the end, I simplified it. Because I have many different formats throughout the sheet, I've added my own format to the cells that contain the date
"'" mm.dd.yyy
This solved that I didn't have to edit the macro.
 

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Thank you for your response. I did it according to your instructions. It worked exactly as you said.
You led me on the right path :)
In the end, I simplified it. Because I have many different formats throughout the sheet, I've added my own format to the cells that contain the date
"'" mm.dd.yyy
This solved that I didn't have to edit the macro.
Great news, all the best!
 

Forum statistics

Threads
1,136,909
Messages
5,678,513
Members
419,768
Latest member
eguechi09x

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