Replacing multiple instances of character

[Colin Foster]

Hi,
Trying to sort out a spreadsheet that someone's given me... a type-up of a
WW2 pilot's logbook.

For one set of information (pilot flying times) has been typed in a single
column using the Spacebar to line up the 4 columns of data (rather than using
4 separate columns).

So, what I want to do is to convert this. So, what I thought was to replace
the spaces with a single comma between each set of data which I could then do
a data parse to separate. Unfortunately, there are various number of spaces
between each of the sets of data, so any suggestions of a quick way of doing
this?

The way that I thought was to use the SUBSTITUTE function & somehow use a *
wildcard to allow it to deal with different numbers of spaces, but either I'm
doing it wrong, or it can't be done that way.

Any suggestions?

Cheers from a snowy & cold Manchester!

 

[Pete_UK]

Yeah, it's also cold and snowy in Warrington !!

Rather than use a formula, you might be better off using some
commands. Find & replace (CTRL-H) will allow you to replace, say, 2
spaces with another character (like "|"). Then you can check down your
column to see that this is what you want. Then you could do Data |
Text-to-columns using that pipe character ("|") as the delimiter,
setting Treat multiple delimiters as one. Finally, use File | Save As
to give the file a different name, so that you still have the original
if you need it.

Hope this helps.

Pete

 

[Otto Moehrbach]

Colin
Say that your data is in Column B starting in B2. In C2 enter the formula:
=Trim(B2)
Using the fill handle (the little square in the bottom right corner of a
selected cell) drag this formula down as far as the Column B data goes.
Now copy all of the occupied cells in Column C and PasteSpecial into B2.
When you select PasteSpecial, you will get a small menu. Click on Values
and click OK. Now delete all of Column B.
What this produces is all the leading and trailing spaces will be deleted as
well as all spaces but one between words.
HTH Otto

 

[Ron Rosenfeld]

Hi,
Trying to sort out a spreadsheet that someone's given me... a type-up of a
WW2 pilot's logbook.

For one set of information (pilot flying times) has been typed in a single
column using the Spacebar to line up the 4 columns of data (rather than using
4 separate columns).

So, what I want to do is to convert this. So, what I thought was to replace
the spaces with a single comma between each set of data which I could then do
a data parse to separate. Unfortunately, there are various number of spaces
between each of the sets of data, so any suggestions of a quick way of doing
this?

The way that I thought was to use the SUBSTITUTE function & somehow use a *
wildcard to allow it to deal with different numbers of spaces, but either I'm
doing it wrong, or it can't be done that way.

Any suggestions?

Cheers from a snowy & cold Manchester!

Why not use the Fixed Width option of the Data/Text-to-Columns wizard?
--ron

 

[Colin Foster]

Pete, Otto & Ron,

Thanks for your responses.

Ron, can't do the fixed width as there are different spaces used, so the
columns don't align
Otto, yours works (forgot that TRIM takes out all intermediate spaces (bar
one))
Pete, your solution gave me an answer, but not exactly as planned - I forgot
that you can set the Text to Columns to treat consecutive delimiters as one,
so I just need to do a straight forward T2C.

Sometimes, I feel like the Great Homer "Doh!"

I'll blame my stupidity on this cold!!

Happy Christmas & thanks for your assistance
Regards
Colin

 

[Ron Rosenfeld]

Ron, can't do the fixed width as there are different spaces used, so the
columns don't align

has been typed in a single
column using the Spacebar to line up the 4 columns of data

you meant there had been spaces inserted so the columns WOULD align.
--ron

 

[Pete_UK]

I thought you might have had single spaces within text that you would
not want to split, hence the suggestion to change 2 spaces to some
other character and use that as the delimiter. Anyway, I glad you
sussed it in the end.

Pete