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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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
 
Upvote 0
There is an option in Text to Columns to 'Treat consecutive delimiters as one.

Why not try setting that as you need?
 
Upvote 0
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
 
Upvote 0
Where are you copying this data from?

How are you actually pasting it in to Excel?

Have you tried Paste Special?
 
Upvote 0
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
 
Upvote 0
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.:)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top