Replacing multiple instances of character
 

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!

Replacing multiple instances of character
 

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

On Dec 20, 3:58*pm, Colin Foster
wrote:
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!

Replacing multiple instances of character
 

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

"Colin Foster" wrote in message
...
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!

Replacing multiple instances of character
 

On Sun, 20 Dec 2009 07:58:01 -0800, Colin Foster
wrote:

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

Replacing multiple instances of character
 

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

"Colin Foster" wrote:

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!

Replacing multiple instances of character
 

On Mon, 21 Dec 2009 04:35:01 -0800, Colin Foster
wrote:

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

Ah, I thought when you wrote:

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

Replacing multiple instances of character
 

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

On Dec 21, 12:35*pm, Colin Foster
wrote:
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



"Colin Foster" wrote:
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!- Hide quoted text -

- Show quoted text -