Remove leading zero in text string

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,038
Office Version
  1. 365
Platform
  1. Windows
Hi

I need to remove the leading zero from the entries that contain it, following the word "Day " in Column I12 onwards, shown below e.g. Day 05534 will become Day 5534.

NB: It should be noted that not all cells begin with this text, but all cells that begin with "Day" will follow with a space and then the leading zero of a 4 digit number, which needs to be removed. Only those cells that begin with with the text "Day 0XXXX" should have the leading zero removed.

Day 05533. 1:05pm, 14-15ºC, overcast. Just a banana and 2pts B4. Felt quite strong, no idea why and no angina (took pills about 2½hrs previously). Almost ran into a berk on a bike near The Fisherman's (pub) coming off the canal towards me ("Pavement, man!! was all I could say). Still felt good when I reached the canal so took it right to the cannon, although felt it starting to drag in the ½ mile approaching it ("how much further?"). Forgot how far it would mean going if I did that and got v tired at times on the return. Back ached along canal, can't be a coincidence with the number of times I've felt that. Wrung out bandana 5+ times, got more and more sweaty as I got more and more dehydrated - actually started to get thirsty over last mile or so. L hamstring started tightening over last 2M + R achilles area. Massive lactic acid build up (?) afterwards, could hardly stand up with the deep aching but as ever, it improved once I sat down. V pleased with the run. Devoured 3 oranges as soon as I got in! Now 2nd longest run ever and Iron Man run No.17!
Indoor bike session, 60 mins.
Well deserved.
Indoor bike session, 60 mins.
Day 05534. 9:15am, 12-14 degrees, overcast and dry, perfect. Had fish & chips for lunch yesterday plus big portion of chips in curry sauce at 7:30pm, got home from Gemma's at 1am and only 4 hours sleep last night. However, didn't suffer for any of it - it was a comfortable run and legs felt quite strong, although back ached a bit in places. NO vaseline on fingers and no biting! V pleased with performance all in all. Iron Man run No.18!

Many thanks!
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,282
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Can't you highlight column "I" and do a Find / Replace ?
Find "Day 0" raplace with "Day "
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,382
Office Version
  1. 365
Platform
  1. Windows
Could you just do a Find/Replace on that column with
Find what: "Day 0" without the quote marks
Replace with: "Day " without the quote marks
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,038
Office Version
  1. 365
Platform
  1. Windows
Thanks to both of you, that's brilliant - I had thought about doing that but I was afraid that all the text after the space would be deleted.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,282
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Glad we could help...(y):cool:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,212
Messages
5,836,034
Members
430,401
Latest member
Kalleahlqvist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top