Cell format

[A2Z CF]

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

[#2]

@A2Z CF 

Perhaps

=--MID(C2,2,LEN(C2)-2)

or

=--SUBSTITUTE(SUBSTITUTE(C2,"=",""),",","")

[#3]

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.

[#4]

@A2Z CF 

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?

[#5]

Yes, here's the link. Thank you

https://docs.google.com/spreadsheets/d/1eNnhy3oOqCgBOL8lL3TI_5ZFFHN20U-7/edit?usp=sharing&ouid=11196... 

[#6]

@A2Z CF 

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.

[#7]

Thank you so much for your response. With the formulas above it appears fine on my end as I mentioned I can fix the data appearance by Date or Text function but not the cell formatting. Therefore when I paste the values into company's original file which is in general format, the values don't show correctly. I think that's because the formula contains "text" so it handled as a text even though the cell formatted as General. Any solution as a Macro will be greatly appreciated. Thank you so much

[#8]

@A2Z CF 

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

[#9]

Perfect! thank you so much, this works great. The only thing is that it does the whole column. My column A range is from 3 to 16 as well as column C. If that part is fixed, I'll be g2g. Thank you so much!!

[#10 · Accepted solution]

@A2Z CF 

Change the line

    For r = 1 To m

to

    For r = 3 To 16

[#11]

That's it! Thank you very much. My sincere appreciation for your time and great help.
May the power be with you!