Dec 01 2022 12:17 AM
Hello all,
I have the formula driven date values in column C. The cells of the column are formatted to show the different forms of dates which begin with “=” and ends with “,” signs.
I want to paste/store the C column values in number or General format not as a text in Column D. I tried my best to accomplish this task but has no luck to succeed in it. Doing so change the data appearance as shown in the example below. Any help in this regard will be greatly appreciated and thank you in advance.
C | D | My desired value in plain number format in column D |
=113022, | 44895 | 113022 |
=112922, | 44894 | 112922 |
=221130, | 44895 | 221130 |
Dec 01 2022 12:38 AM
Dec 01 2022 01:41 AM - edited Dec 01 2022 01:49 AM
Thank you so much for your response, it still shows the date as mentioned in column D. I want the column C date format to be appear in column D as mentioned in next column "My desired value...". (I can get my desire value by converting the column D's cell as Date Format or as a text, but I want the value in plain Number or General format of the cell as mentioned in the next column "My desired value..." Like capturing the data in column C and pasting it to the column D without "=" and coma signs and without Date format of the cell). Thank you.
Dec 01 2022 03:24 AM
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Dec 01 2022 07:53 AM - edited Dec 01 2022 07:56 AM
Yes, here's the link. Thank you
Dec 01 2022 12:10 PM
The problem is that:
A1 and A2 contain numbers (of days) formatted as =0\,
A4, A5 and A7 contain dates formatted as=mmddyy,
A8, A10 and A11 contain dates with custom format =yymmdd,
So you have two completely different types of values, with three different types of formatting.
You cannot expect a single formula to handle that. (It could probably be done with VBA)
In C1, simply use =A1
In C4, use =--TEXT(A4,"mmddyy")
In C8, use =--TEXT(A8,"yymmdd")
Format column C as General.
Dec 01 2022 04:13 PM
Dec 02 2022 03:04 AM
See if this does what you want:
Sub Convert2Number()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
m = Range("A" & Rows.Count).End(xlUp).Row
For r = 1 To m
If Range("A" & r).Value <> "" Then
Range("C" & r).Value = Replace(Replace(Range("A" & r).Text, "=", ""), ",", "")
End If
Next r
Application.ScreenUpdating = True
End Sub
Dec 02 2022 08:07 AM
Dec 02 2022 08:16 AM
SolutionDec 02 2022 08:39 AM
Dec 02 2022 08:16 AM
Solution