Remove leading zero in text string

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 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
51,743
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
858
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,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad we could help...(y):cool:
 

Forum statistics

Threads
1,147,735
Messages
5,742,869
Members
423,760
Latest member
photogfrog

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