Extra space in cells

M

mamen

New Member
Joined
Apr 15, 2010
Messages
6
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
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
 
Upvote 0
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 {
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:biggrin:

Gary
 
Upvote 0
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.
 
Upvote 0
What does he need a double space for? In the original posts he states "If blank space is > 1". Seems that =TRIM would work....
 
Upvote 0
You must log in or register to reply here.

Similar threads

Atholl
  • Solved
Adaptive x axis spacing on change of y value
Replies
3
Views
138
JamesCanale
JamesCanale
AndyTampa
  • Solved
Parse one cell with 1 - 4 case numbers into userform
Replies
7
Views
121
AndyTampa
AndyTampa
D
  • Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
686
diversification
D
H
  • Solved
Need to remove spaces from cells
Replies
5
Views
767
Fluff
Fluff
M
  • Question
VBA to Clean Up CSV with Large Blank Spaces and Extra Text Rows
Replies
5
Views
476
kevin9999
K

Forum statistics

Threads
1,216,119
Messages
6,128,947
Members
449,480
Latest member
yesitisasport

Share this page

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