Copied two columns from web site

kojak43

Active Member
Joined
Feb 23, 2002
Messages
270
When I pasted the two columns x 60 rows, Excel put everything into A1.
So it appears as 16055spacespaceText Textspacespace8956spacespaceTextText and so on.
I thought I could do a find and replace. I wanted Excel to find spacespace and replace it with ,spacespace. That would give me a comma delimited file and I could flow it in as two columns one numeric in A1 and Text in B1.

I am using Excel 2000 with W2K. Excel 2000 does not recognize spaces as a character so the find and replace thing does not work. Is there a work around?

Oh, yes, spacespace is my way of showing you all that there are two space characters between the numbers and the text.

k
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,056
Office Version
  1. 365
Platform
  1. Windows
Have you tried Data>Text to Columns... with space as a delimiter?

By the way as far as I know Excel will recognise spaces in a find and replace.
 

kojak43

Active Member
Joined
Feb 23, 2002
Messages
270
Norie, Thanks for the prompt reply

Yes, I have tried Data>Text to Columns.
The problem with that is Excel treats consecutive spaces as one space. while it allows for a column, it also puts in a column where it sees single spaces between the text words. So Text to Columns puts in the "bar" at each space. I have to remove those "bars" so the text reads correctly. It is very, very difficult to remove those "bars" manually.

As far as Excel recognizing spaces, not on my machine. I even copied the two spaces and pasted them into the find box. It would not recognize.

I know it can be done. I just can not do it.

Thanks
k
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,056
Office Version
  1. 365
Platform
  1. Windows
There is an option in Text to Columns to 'Treat consecutive delimiters as one.

Why not try setting that as you need?
 

kojak43

Active Member
Joined
Feb 23, 2002
Messages
270

ADVERTISEMENT

I had tried that as well. However, treating consecutive as one does not eliminate the one space between text words. That is where Excel places the "bar".

Oh yeah, when I manually remove the extra "bars" and flow into Excel it puts the first number in A1, the first text in B1 then the next number in C1.
I need all the numbers in A1 and the text in B1. When I tried to transpose (via paste special>transpose, it put everything into A1.

When I do the find and replace I put in two keyboard spaces in find. Then in replace I put in ,space space.

When I try to replace it tells me formula is too long. When I just put in two spaces and try a find. It says it does not recognize the characters.

k
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,056
Office Version
  1. 365
Platform
  1. Windows
Where are you copying this data from?

How are you actually pasting it in to Excel?

Have you tried Paste Special?
 

kojak43

Active Member
Joined
Feb 23, 2002
Messages
270
OMG! I just tried doing it all over again and pasted special as text into a blank worksheet and it worked perfectly.

Thank you Norie

I can not tell you how pleased I am with your guidance!!!

k
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,056
Office Version
  1. 365
Platform
  1. Windows
Glad you got it at last.:)

I've had problems in the past pasting in to Excel from the web, including this site.

I would say that what I offered was more guess work than guidance.

But anyway, you got it sorted.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,318
Messages
5,571,527
Members
412,401
Latest member
allenayres83
Top