Hello, I need a better way (rather than indivdually) to add a space before PM or AM in a cell range that looks like this:
04:06PM
04:19PM
04:32PM
08:42PM
11:00AM
12:01PM
06:13AM
09:35PM
06:18AM
09:00PM
12:34PM
12:37PM
07:22AM
In other words a space before the last two letters of every cell in colum C
Thanks
=if(iserr(find("am",c16,1)),substitute(c16,"pm"," pm"),substitute(c16,"am"," am"))
Perhaps
=LEFT(A1,LEN(A1)-2) &" "&RIGHT(A1,2)
Hi kane12
Try the following and format the cells as "hh:mm AM/PM"
Formula:
Please Login or Register to view this content.
Regards Kevin
Merged Cells (They are the work of the devil!!!)
I can't figure it out. My data is in Column C, cell C3 - C2642. Don't know how to begin.
Thanks
I can"t get any of these solutions to work. Maybe I'm not using them correctly. Any help would be MUCH appreciated. I have much more work doing this task of adding spaces. A macro ? A formula ? I am a novice at Excel.
Thanks !
Maybe..
EDIT: Changed to column C, as per OP requirement
Please Login or Register to view this content.
Last edited by Ace_XL; 02-22-2013 at 11:46 AM.
Life's a spreadsheet, Excel!
Say thanks, Click *
My data is in column c, does that make a difference ?Originally Posted by Ace_XL
Hi kane12
Good point!
=REPLACE(C1,LEN(C1)-1,0," ")+0
It makes every cell say 12:00 AM
I have blank cells in c1, c2 and my data starts in c3. I put the formula in c1 and drag down.
Ensure that calculation settings are set to 'automatic', else press F9 after dragging down. If it still dosent work upload a sample worksheet
This formula made every cell say 12:00 AM
automatic ?? where do I set that ?
It displays the correct result for me! See the attached.
how do I attach a sample spreadsheet ?
Go Advanced an it is the paper clip.
Try this :
Note : don't forget format time : 1:30 PM or custom : h:mm AM/PMPlease Login or Register to view this content.
Last edited by SDCh; 02-22-2013 at 12:30 PM. Reason: Change code
Click (*) if you received helpful response.
Regards,
David
sample file to play with
What do you want to do!
Wow ... still changes everything to 12:00 AM. I'm doing something wrong. Thanks for your patience !
find replace should do it
find PM replace {space}PM
repeat for AM
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Add a space before AM or PM without changing the time. I see it works on your example but I can't make it work on mine.
kane12,
Your data already on format time, and not like what you said on post #1, that why we all get wrong.
Regards
SDCh
Last edited by SDCh; 02-22-2013 at 12:37 PM.
kane12
That is time you have in column C. If you click on C1 for example > Format as general. You will see a number. that is why none of these formulas are working!
That worked !! HURAY !!!
Thank you all so much for helping !!!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks