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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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,344
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,344
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,344
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.:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,052
Messages
5,835,142
Members
430,343
Latest member
Sailingexcel

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