Extra space in cells

[mamen]

Here is my problem: I need to split cells but I need to do it with some kind of conditional formatting.

Right now these cells have "(" "," " " so the only thing that is different is that in between what i want to split there are at least 2 spaces blank or more.

What i thought about is to have a formula that goes like: If blank space is > 1 then replace with "{". Then after that's done, it will be easy to split text to columns using "{". I Hope i make any sense.

Remember the data in all these cells is different with numbers, text different length and the only thing that makes a difference is space in between the characters is > 1.

 

[Gary McMaster]

How about just using a mass find & replace. Replace 2 spaces with a single character "{" or whatever else you may want to use as a delimiter.

Gary

 

[mamen]

I wish I could do that but some cells have extra 2 others have extra 4, 20, 10 so if i do the replace of 2 spaces for { when others have more, it will give me a lot of {

 

[Gary McMaster]

You can still make it work. Start with the largest number of spaces and work your way down to 2.

Crude for sure but probably faster than writing code or formulas.

20 spaces to "{"
19 spaces to "{"
...

Gary

 

[mamen]

that sounds so painful. I have thousands of records. I don't know what's the max amount of extra spaces. I guess I was hoping someone had that same problem and found a solution.

Thanks a lot for helping me out!!

Cheers!
Mamen

 

[Gary McMaster]

I'm sure someone has. They'll be along shortly.

My solution is great if you happen to have a trained monkey and a bunch of bananas

Gary

 

[sous2817]

Can you give a specific example of what it in a cell?

 

[sous2817]

Actually, wouldn't the =TRIM function work?

 

[bkaehny]

I don't think you want to trim because you need the double spaces. You could 3 spaces and replace it with 2 spaces until you get no more matches, then replace 2 spaces with whatever symbol you want to use as a delimiter. Should take no more than 30 seconds to do.

 

[sous2817]

What does he need a double space for? In the original posts he states "If blank space is > 1". Seems that =TRIM would work....