Adding A Space Within Text

  • Thread starter Thread starter Mediaexcel
  • Start date Start date
M

Mediaexcel

Hello

I am trying to convert a column into time format.
This is how I got it

03:38:27AM
10:08:27AM
01:36:59AM
01:51:56AM
03:56:22AM
04:17:06AM
04:39:35AM
06:13:32AM
09:22:30AM
10:17:58AM
10:24:40PM
10:47:21AM
11:47:24PM

For excel to recognize it in TIME format, I have to insert a spac
before the AM or PM.

Any suggestions
 
The easist way would be to search for "AM" and replace with " AM", search
for "PM" and replace with " PM"


Dave R. said:
Luckily the times are all the same size..

=LEFT(A1,8)&" "&RIGHT(A1,2)
Click to expand...
 
Hi Dave this won't work as Excel still would treat the formula result
as text. I'd add the following:
=--(LEFT(A1,8)&" "&RIGHT(A1,2))
and format the cell as time
 
Hi Frank,

It works for me. While it doesn't change the appearance other than the space
(i.e. A1 can be 12:21:15pm, it will be converted to 12:21:15 pm), it does
count it as more than text because you can add/subtract from it and not get
#VALUE! as you would if you were adding/subtracting from text.
 
Hi Dave
interesting. For me this returns a string (Excel 2003, target cell
formated as 'General'). I had to add the unary operator
 
I tried it one more time for good measure (Excel 2003), and the formula
returns 12:20:15 pm
in B1

In F1 formatted as general, I use =B1+1 and get
1.514063
 
Hi Dave
this works also for me but try applying a different time format to cell
B1. This won't work. IMHO Excel converts the text value due to your
addition in cell F1.
 
Ahh I see, Thanks!


Frank Kabel said:
Hi Dave
this works also for me but try applying a different time format to cell
B1. This won't work. IMHO Excel converts the text value due to your
addition in cell F1.
Click to expand...
 
Thank you both ways worked just fine....

I have another little problem.

One of the columns has the values that I need to convert to numbe
format, however it seems that the values have a space before them an
therefore I cannot format it as a number.

I have attached the column as I got it.

thank you for your help

S

Attachment filename: number format example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52234
 
Back
Top