Forum Discussion
Remove end comma
I have a report that I exported into Excel 2010. The report has a date field 08/31/2018. When the report comes over to excel, an end comma is placed after the date "08/31/2018,". This report can be 50-250 lines. How can I easily remove the end comma without having to manually remove it from each line? Is there a formula or macro that can make this happen? The date appears in column "D".
Thank you!
6 Replies
As variant you may select cells with such dates, Ctrl+H and replace comma on nothing
SergeiBaklan, if I have multiple dates, is there an easy way of doing this? The report spans the whole month, so I will have multiple dates with the comma at the end. I might also have multiple lines with the same date. I suppose I just need to CTRL+H for each date that appears...
Karen, you may select cells from D123 to D152 (or so), after that press Ctrl+H and remove the comma
If your date is in A1 just use this formula:
=DATEVALUE(MID(A1,1,10))
Since mid is a string function it will turn your date to a string. So we need to convert back to date. Make sure the column is formatted as a date otherwise you will see numbers like this: 43343
Matt Mickle, the dates are in all in column D. D123, D124, D125, etc through D152 for this report. In your formula, I know what the A1 is (column location) but where do the 1 and 10 correlate?
The 1 and the 10 refer to the character #'s
01/01/2018
Start on 0 (1st Character) End on 8 (10th character)
You could also use LEFT()
=DATEVALUE(LEFT(A1,10))
