Horse Racing Forum - PaceAdvantage.Com - Horse Racing Message Board

Go Back   Horse Racing Forum - PaceAdvantage.Com - Horse Racing Message Board > Off Topic > Off Topic - Computers


Reply
 
Thread Tools Rate Thread
Old 10-26-2018, 01:27 AM   #1
Tom
The Voice of Reason!
 
Tom's Avatar
 
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?
Tom is online now   Reply With Quote Reply
Old 10-26-2018, 01:45 AM   #2
jay68802
Registered User
 
jay68802's Avatar
 
Join Date: May 2008
Location: Nebraska
Posts: 15,110
Try

=SPLIT(A?," ")
jay68802 is offline   Reply With Quote Reply
Old 10-26-2018, 06:58 AM   #3
rispa
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).
rispa is offline   Reply With Quote Reply
Old 10-26-2018, 08:52 AM   #4
Tom
The Voice of Reason!
 
Tom's Avatar
 
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?
Tom is online now   Reply With Quote Reply
Old 10-26-2018, 08:55 AM   #5
Tom
The Voice of Reason!
 
Tom's Avatar
 
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,470
Quote:
Originally Posted by jay68802 View Post
Try

=SPLIT(A?," ")
I get a function not valid comment.
__________________
Who does the Racing Form Detective like in this one?
Tom is online now   Reply With Quote Reply
Old 10-26-2018, 09:10 AM   #6
rispa
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?
rispa is offline   Reply With Quote Reply
Old 10-26-2018, 10:29 AM   #7
Tom
The Voice of Reason!
 
Tom's Avatar
 
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.
Tom is online now   Reply With Quote Reply
Old 10-26-2018, 11:43 AM   #8
spiketoo
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.
spiketoo is offline   Reply With Quote Reply
Old 10-26-2018, 03:57 PM   #9
Tom
The Voice of Reason!
 
Tom's Avatar
 
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?
Tom is online now   Reply With Quote Reply
Old 10-26-2018, 08:44 PM   #10
AltonKelsey
Veteran
 
AltonKelsey's Avatar
 
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
AltonKelsey is offline   Reply With Quote Reply
Old 10-26-2018, 11:09 PM   #11
cj
@TimeformUSfigs
 
cj's Avatar
 
Join Date: Jan 2002
Location: Moore, OK
Posts: 46,816
Quote:
Originally Posted by Tom View Post
Clean as no effect.

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

Where the initial data is in A1.

Attached Images
File Type: png ExcelFormula.png (2.6 KB, 82 views)

Last edited by cj; 10-26-2018 at 11:11 PM.
cj is offline   Reply With Quote Reply
Old 10-26-2018, 11:13 PM   #12
Suff
Beat up 💪
 
Suff's Avatar
 
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.
Suff is offline   Reply With Quote Reply
Old 10-27-2018, 02:29 PM   #13
Tom
The Voice of Reason!
 
Tom's Avatar
 
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?
Tom is online now   Reply With Quote Reply
Old 10-28-2018, 09:51 AM   #14
rispa
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..........
rispa is offline   Reply With Quote Reply
Old 10-28-2018, 11:38 AM   #15
cj
@TimeformUSfigs
 
cj's Avatar
 
Join Date: Jan 2002
Location: Moore, OK
Posts: 46,816
Quote:
Originally Posted by Tom View Post
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.
cj is offline   Reply With Quote Reply
Reply




Thread Tools
Rate This Thread
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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

» Advertisement
Powered by vBadvanced CMPS v3.2.3

All times are GMT -4. The time now is 09:35 PM.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Copyright 1999 - 2023 -- PaceAdvantage.Com -- All Rights Reserved
We are a participant in the Amazon Services LLC Associates Program, an affiliate advertising program
designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites.