Deleting Last 8 Characters From A String (Difficulty w/ Formatting)

[ssshah]

Hi Everybody,

I've gone through multiple forums and exhausted Google search for this question. I often find that deleting the last x characters is no problem, but I have a formatting issue with my data.

Version: Excel 2010
OS: Windows XP

I want to go through column B and delete the last 8 characters in the string if they contain " x". Where x can be any single digit number. There are 7 spaces in that blank spot before x. It is quite cumbersome to go through all the iterations for find & replace is there a more intelligent way to do this via macro? I'd like to also make a note that the cells without " x" the formatting is tricky to deal with because if put into text form it displays as a number between 0 and 1 (ex. 0.583). So I want to remove the last 8 digits & correctly format the time if " x" exists.

I don't think I can upload the file here, so please private message me if you can help! Or if you don't need the file post here!


Example of file

13:15       1
13:30
13:45
14:00
14:15
14:30       7
14:45       3

Oh I used HTML code but its only to highlight the example.

Thanks in advance

-ssshah

 

[Jonmo1]

Try a formula

=REPLACE(A1,FIND(" ",A1&" "),LEN(A1),"")+0

 

[ssshah]

Works perfectly, I have to still get around the formatting but that I can do. Thank you so much for your help!

 

[Jonmo1]

Glad to help, thansk for the feedback.

Yes, you just have to format the cell with the formula as Number.

You can also try using
Data - Text To Columns - Deliminated - Space
Make sure to check "treat consecutive delimiters as one"

 

[Greg Truby]

...You can also try using
Data - Text To Columns - Deliminated - Space
Make sure to check "treat consecutive delimiters as one"

Actually, I was going to suggest the Text to Columns tool as well, but using fixed width (if the times are all HH:MM formatted) and then just picking "Skip" for the 2<SUP>nd</SUP> column.