Find and Replace?

[Guest]

Hi,
DOes anyone know of a find and replace function that will search through
cells and located
:23:45
:12:45
:34:54
And change to
0:23:45
0:12:45
0:34:54

In addition there are numbers in there already that have leading digits
(1:24:34 for example) I don't want the formula to alter those numbers.

Thanks in advance,

Intotao

 

[Frank Kabel]

Hi
a formula solution. In an adjacent cell enter the formula:
=--("0"&A1)
and copy this down for all rows (format this column as 'Time')
- after this copy this columns and insert it with 'Edit - Paste Special -
Values'.
- After this you may delete the original column

 

[Guest]

Hi Frank,
I know you've helped me with this before (apologies) But Here's what
happened when I tried your suggestion. The first time I copy the cells and
pasted special "values" it worked! I was thrilled to say the least. However,
after numerous tests, it will not do it again. Now when I try, I get the same
value I pasted 23:53) On the off chance it was the column formatted
incorrectly, I did it again in a different column...still no luck. Then I
tried another worksheet, same. Finally I opened a new book - formatted the
whole thing time and STILL no luck. Couple of questions?
The first time I wrote the formula in a cell - I got "#value" (This is when
it worked) Now all I get is 0:00:00 when I've finished entering. Which is the
correct way? Second - the reference to A1...does A1 have to be an empty cell?
Do I need to ensure it's formatted as time as well? Do I need to increment
for each row? ie
=--("0"&A1)
=--("0"&B1)
=--("0"&C1)

Etc.?

Sorry, but since last week this thing is driving me nuts!
Any insight would be greatly appreciated...

Intotao

 

[Frank Kabel]

Hi
if you like email me an example sheet.

 

[Max]

Just another option to try ..

Assuming all of the sample data below is text,
and are in col A, A1 down

:23:45
:12:45
:34:54
1:24:34

Put in B1: =IF(LEFT(A1,SEARCH(":",A1))=":","0"&A1,A1)
Copy down

 

[Guest]

Hi Frank,
Did you get it?

Intotao

Hi
if you like email me an example sheet.

 

[Max]

There's another option for you to try in the interim <g>
(posted in the other branch) ..

Here's the gist of it ..

Assuming all of the sample data below is text,
and are in col A, A1 down

:23:45
:12:45
:34:54
1:24:34

Put in B1: =IF(LEFT(A1,SEARCH(":",A1))=":","0"&A1,A1)
Copy down

 

[Harlan Grove]

Just another option to try .. ....
Put in B1: =IF(LEFT(A1,SEARCH(":",A1))=":","0"&A1,A1)
Copy down

?!

LEFT(A1,SEARCH(":",A1))=":"

can only be true when

LEFT(A1,1)=":"

is true. Simpler is usually better.

 

[Max]

LEFT(A1,SEARCH(":",A1))=":"
can only be true when
LEFT(A1,1)=":"
is true. Simpler is usually better.

Agreed. Thanks for the correction, Harlan.
(Got lost searching for the colon <g>)

 

[Max]

Simpler to use instead in B1, as pointed out by Harlan ..

=IF(LEFT(A1,1)=":","0"&A1,A1)

 

[Guest]

Copy the cells to Word.

In Word:

Go to Edit Replace

Enable Wildcards

Replace ([0-9]{1-2}([0-9]{2}:[0-9]{2}) with \1Â¥\2

Replace [0-9]{2}:[0-9]{2}) with 0\1

Replace ¥ with nothing (leave Replace With field blank)

Copy and paste back to Excel

 

[Guest]

All I can say is thanks guys!
Martin, Frank, Max and Harlan. I think I can work with something among all
of these great ideas.

Many thanks, I NEVER would have figured any of this out!

Intotao

Copy the cells to Word.

In Word:

Go to Edit Replace

Enable Wildcards

Replace ([0-9]{1-2}([0-9]{2}:[0-9]{2}) with \1Â¥\2

Replace [0-9]{2}:[0-9]{2}) with 0\1

Replace ¥ with nothing (leave Replace With field blank)

Copy and paste back to Excel

Hi,
DOes anyone know of a find and replace function that will search through
cells and located
:23:45
:12:45
:34:54
And change to
0:23:45
0:12:45
0:34:54

In addition there are numbers in there already that have leading digits
(1:24:34 for example) I don't want the formula to alter those numbers.

Thanks in advance,

Intotao