Excel Help

[Tom]

I have a column of data I want to use Text to columns to fix.
The data is not lined up in every cell.

3 MSW
3 MC5000
3 Clm10000
3 STR

When I get rid of the first column of numbers, there is an extra space on the left in many cells.
I tried using TRIM but it doesn't work.

Any suggestions?

TY

[jay68802]

Try

=SPLIT(A?," ")

[rispa]

Try using the Text to Columns function:

Either delimited by space or use Fixed width (importing only the column you want).

[Tom]

I'm using Text to Columns, but I still get the text off set.
The example I used straightened itself out in my post!
Looks like this:

3 MSW
3... MC5000
3 Clm10000
3 ...STR

Then after T2C it looks like this

MSW
... MC5000
Clm10000
...STR

[Tom]

Try

=SPLIT(A?," ")

I get a function not valid comment.

[rispa]

Try this.

Highlight entire row first. Then do text to columns. Choose delimit by space.

Are you taking the data from DRF Winner's books?

[Tom]

I am merging several old Excel sheets into one so I can put into Access.
Some of the data came from comma files, and were spaced odd.

I'll try that T2C method.
I've been using fixed.

Just tried - nope - splits in to too many columns - like, Md Sp Wt is not 3 columns.

I have a manual workaround, just takes a long time.

TY

[spiketoo]

Shouldn't be any issues if imported from previous xls but try using the CLEAN function to strip out any funky data that may be present before you go T to C.

[Tom]

Clean as no effect.

[AltonKelsey]

I tried your posted example and it works for me




Did you click the check box on screen two of the splitter "treat consecutive delimiters as one"




If thats not it, upload a sample file, and Ill look at it

[cj]

Clean as no effect.


=SUBSTITUTE(RIGHT(A1,LEN(A1)-1)," ","")

Where the initial data is in A1.

[Suff]

Could you use find and replace? In the find box hit the space bar, in the replace box put nothing. replace all. done.

[Tom]

CJ - will try that.
Suff - tried that - no luck.
Alton-
I'm using Excel 2000, so maybe some of those things weren't around back then.

[rispa]

If you would like to send me some of the data, I would give it a whirl and try to help. I'm using Excel 2003.



Just a thought..........

[cj]

CJ - will try that.
Suff - tried that - no luck.
Alton-
I'm using Excel 2000, so maybe some of those things weren't around back then.

Cool, let me know. Possible the white space is tab or something else if that doesn't work. I just replaced your dots in the example with spaces.

[formula_2002]

is this what you want?

[Tom]

is this what you want?

Yes. I do not need the column with the numbers, just the other one, justified to the left,

[cj]

Did my suggestion not work? If not the white space is something different than spaces. Email it to me and I'll figure it out pretty quick I hope.

[Tom]

Thanks, CJ.
I found a quick way to work around - sort the column then use text to columns on the segments with the same number of spaces and it works quick.

I will try your suggestion tonight.
I have a lot more files to fix up.

[Tom]

Yes! It worked.
Did a couple of files and they all came out good

Thank You!!!!!

[formula_2002]

is this what you want?

attained the results this way
DATA
HIGHLIGHT COLUMN
TEXT TO COLUMNS
DELIMITED
SPACE
FINISH

[Tom]

I tried that p it separates the data as well, ie, instead getting
[Md Sp Wt], I get [ md ][ sp ][ wt ] in 3 columns.

[cj]

Yes! It worked.
Did a couple of files and they all came out good

Thank You!!!!!

Awesome!