Convert Text formatted as Time into Text in Excel

Question

I have a set of data downloaded through a PHP script - One particular column has data as follows

 8 A, 30 A, 35 A, 100 A, 12 A

Now excel automatically converts them into the following format

8:00 AM, 30 A, 35 A, 100 A, 12:00 AM

This auto-conversion to time format is quite frustating for all numbers less than 24.

I have tried the below regexreplace through a custom vba function

 =regexreplace(TextCell, A$, "")

The regex works on all the other As but not this one. Is there any solution for this ?

Answer 1

The regex works on all the other As but not this one. Is there any solution for this ?

I'm not sure if I understand what you need exactly? Please give me an example of what you want to do with this regex ?

If you want go back to the originally value then you can simply format value of time (DateTime is stored like as Double), example:

Dim txt As Strig
txt = Format(Cells(1, 1).Value, "0 A")

Please remember set data format of cell/column to 'Text' - if you want store proper value in sheet, example:

Range("A1").NumberFormat = "@"

Range("A:A").EntireColumn.NumberFormat = "@"

This auto-conversion to time format is quite frustration for all numbers less than 24.

Please give more information about when it's converted and how you use this file?

I'm asking because, probably you can turn-off this 'auto-converting' and perhaps it will be good way.

For example if you have CSV file, then when you importing this file in Excel - you must go through Dialog with 'import settings'... and you can set format of column as 'Text', then it will be imported like 'as is'.

Answer 2

Well, a quick solution to format the values back to your format would be:

=IF(ISNUMBER(A1),A1*24&" A",A1)

or:

=IF(ISNUMBER(A1),HOUR(A1)&" A",A1)