|
|
10-26-2018, 01:27 AM
|
#1
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,470
|
Excel Help
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
__________________
Who does the Racing Form Detective like in this one?
|
|
|
10-26-2018, 01:45 AM
|
#2
|
Registered User
Join Date: May 2008
Location: Nebraska
Posts: 15,110
|
Try
=SPLIT(A?," ")
|
|
|
10-26-2018, 06:58 AM
|
#3
|
Registered User
Join Date: Apr 2008
Posts: 159
|
Try using the Text to Columns function:
Either delimited by space or use Fixed width (importing only the column you want).
|
|
|
10-26-2018, 08:52 AM
|
#4
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,470
|
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
__________________
Who does the Racing Form Detective like in this one?
|
|
|
10-26-2018, 08:55 AM
|
#5
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,470
|
Quote:
Originally Posted by jay68802
Try
=SPLIT(A?," ")
|
I get a function not valid comment.
__________________
Who does the Racing Form Detective like in this one?
|
|
|
10-26-2018, 09:10 AM
|
#6
|
Registered User
Join Date: Apr 2008
Posts: 159
|
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?
|
|
|
10-26-2018, 10:29 AM
|
#7
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,470
|
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
__________________
Who does the Racing Form Detective like in this one?
Last edited by Tom; 10-26-2018 at 10:32 AM.
|
|
|
10-26-2018, 11:43 AM
|
#8
|
Registered User
Join Date: Sep 2008
Posts: 361
|
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.
|
|
|
10-26-2018, 03:57 PM
|
#9
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,470
|
Clean as no effect.
__________________
Who does the Racing Form Detective like in this one?
|
|
|
10-26-2018, 08:44 PM
|
#10
|
Veteran
Join Date: May 2016
Posts: 1,831
|
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
|
|
|
10-26-2018, 11:09 PM
|
#11
|
@TimeformUSfigs
Join Date: Jan 2002
Location: Moore, OK
Posts: 46,816
|
Quote:
Originally Posted by Tom
Clean as no effect.
|
=SUBSTITUTE(RIGHT(A1,LEN(A1)-1)," ","")
Where the initial data is in A1.
Last edited by cj; 10-26-2018 at 11:11 PM.
|
|
|
10-26-2018, 11:13 PM
|
#12
|
Beat up 💪
Join Date: Jun 2002
Location: Beach life in Fort Lauderdale
Posts: 11,938
|
Could you use find and replace? In the find box hit the space bar, in the replace box put nothing. replace all. done.
|
|
|
10-27-2018, 02:29 PM
|
#13
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,470
|
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.
__________________
Who does the Racing Form Detective like in this one?
|
|
|
10-28-2018, 09:51 AM
|
#14
|
Registered User
Join Date: Apr 2008
Posts: 159
|
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..........
|
|
|
10-28-2018, 11:38 AM
|
#15
|
@TimeformUSfigs
Join Date: Jan 2002
Location: Moore, OK
Posts: 46,816
|
Quote:
Originally Posted by 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.
|
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.
|
|
|
|
|
Thread Tools |
|
Rate This Thread |
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|