**Excel help, Advanced ** - DFWstangs Forums
 
LinkBack Thread Tools Display Modes
post #1 of 14 (permalink) Old 11-18-2009, 02:02 PM Thread Starter
AHMO!
 
Join Date: Apr 2004
Location: the original let me google that for ya guy
Posts: 16,810
**Excel help, Advanced **

Ok, lets say I have a 10 digit #
Example 3221400011
I want to break it down automatically for certain positions (1-10) to be in certain cells.

Example I want three positions to be in Cell A2, 4th be in A3, 5th in A4, 6-9th in A5, and 10th in A6.

So the following # 3221400011 would end up like this
A2 = 322
A3 = 1
A4 = 4
A5 = 0001
A6 = 1

Yes I know I can automatically type this in but I want to be able to put the 10 digit # in field A1 it fill in fields A2-A6 so my If statements validate the payment info.

Any help would be greatly appreciated.
Nate is offline  
Sponsored Links
Advertisement
 
post #2 of 14 (permalink) Old 11-18-2009, 02:11 PM
Very Interesting
 
The Big Matt's Avatar
 
Join Date: Mar 2000
Location: Around the World
Posts: 9,856
super easy

Highlight the entire column. Then go to Data, and chosse Text to Columns.

Choose Fixed Width, and then place a line between each number group.

Done

You're only as strong as you allow yourself to be...

Lockout Workout Forums and Supplements
The Big Matt is offline  
post #3 of 14 (permalink) Old 11-18-2009, 02:22 PM
dead
 
Join Date: Sep 2002
Posts: 14,611
formula for a2: =LEFT(A1, 3) or =MID(A1,1,3)
formula for a3: =MID(A1,4,1)
formula for a4: =MID(A1,5,1)
formula for a5: =MID(A1,6,4)
formula for a6: =MID(A1,10,1) or =RIGHT(A1,1)

you just have to make sure A1 has 10 digits and that will take care of the rest.
momo stallion is offline  
 
post #4 of 14 (permalink) Old 11-18-2009, 02:37 PM Thread Starter
AHMO!
 
Join Date: Apr 2004
Location: the original let me google that for ya guy
Posts: 16,810
Quote:
Originally Posted by The Big Matt View Post
super easy

Highlight the entire column. Then go to Data, and chosse Text to Columns.

Choose Fixed Width, and then place a line between each number group.

Done
No, I want this to be a file where I type into A1 and then it spits out what the data means.

i.e first three posistions are 322 which would equate to the 322nd day of the year so it will spit out 11/18. I don't want to text to colums every time.
Nate is offline  
post #5 of 14 (permalink) Old 11-18-2009, 02:39 PM
Punk Ass Newbie
 
Chuck's Avatar
 
Join Date: Sep 2003
Location: Providence Village
Posts: 4,554
Quote:
Originally Posted by momo stallion View Post
formula for a2: =LEFT(A1, 3) or =MID(A1,1,3)
formula for a3: =MID(A1,4,1)
formula for a4: =MID(A1,5,1)
formula for a5: =MID(A1,6,4)
formula for a6: =MID(A1,10,1) or =RIGHT(A1,1)

you just have to make sure A1 has 10 digits and that will take care of the rest.
This

Chuck
2001 Black Bullitt (For Sale)
2009 Black JK Unlimited X, M6, 35's
www.imboc.com
Rob (Tweakd) Memorial 3/13/05
Chuck is offline  
post #6 of 14 (permalink) Old 11-18-2009, 02:40 PM Thread Starter
AHMO!
 
Join Date: Apr 2004
Location: the original let me google that for ya guy
Posts: 16,810
Quote:
Originally Posted by Chuck View Post
This
Yep mid function is working except for A6 is giving me an error with my IF statement.
Nate is offline  
post #7 of 14 (permalink) Old 11-18-2009, 02:45 PM
Punk Ass Newbie
 
Chuck's Avatar
 
Join Date: Sep 2003
Location: Providence Village
Posts: 4,554
Quote:
Originally Posted by Nate View Post
Yep mid function is working except for A6 is giving me an error with my IF statement.
ID 10 T error.

Chuck
2001 Black Bullitt (For Sale)
2009 Black JK Unlimited X, M6, 35's
www.imboc.com
Rob (Tweakd) Memorial 3/13/05
Chuck is offline  
post #8 of 14 (permalink) Old 11-18-2009, 02:47 PM Thread Starter
AHMO!
 
Join Date: Apr 2004
Location: the original let me google that for ya guy
Posts: 16,810
Quote:
Originally Posted by Chuck View Post
ID 10 T error.
Definitely.
Nate is offline  
post #9 of 14 (permalink) Old 11-18-2009, 02:51 PM
dead
 
Join Date: Sep 2002
Posts: 14,611
Quote:
Originally Posted by Nate View Post
Yep mid function is working except for A6 is giving me an error with my IF statement.
post your IF statement
momo stallion is offline  
post #10 of 14 (permalink) Old 11-18-2009, 03:31 PM
Lifer
 
Avery'sDad's Avatar
 
Join Date: Feb 2008
Location: Red Oak
Posts: 1,509
I think he meant you could use either the mid or right function. Shouldn't be an IF statement.
Avery'sDad is offline  
post #11 of 14 (permalink) Old 11-18-2009, 03:36 PM Thread Starter
AHMO!
 
Join Date: Apr 2004
Location: the original let me google that for ya guy
Posts: 16,810
Quote:
Originally Posted by momo stallion View Post
post your IF statement
The If statement for A3
=IF(A3=0, "$0-99", IF(A3=1, "$100-199", IF(A3=2, "$200-299", IF(A3=3, "$300-399", IF(A3=4, "$400-499",IF(A3=5, "$500-599", IF(A3=6, "$600-699", IF(A3=7, "$700-799",IF(A3=8, "$800-899", IF(A3=9, "$900-999", "void"))))))))))


IF statement for A6
=IF(A6=1,"Cash", IF(A6=2, "Check", IF(A6=3, "Multiple", IF(A6=4,"MO or CC", "Void"))))

***I am using these statements in Colum B if that wasn't clear to break down the original 10 digit #.
Both IF statements were working when I manually enter the data but when I use the MID foruma's they return voids.
Nate is offline  
post #12 of 14 (permalink) Old 11-18-2009, 03:53 PM
Punk Ass Newbie
 
Chuck's Avatar
 
Join Date: Sep 2003
Location: Providence Village
Posts: 4,554
=IF(A6="1","Cash", IF(A6="2", "Check", IF(A6="3", "Multiple", IF(A6="4","MO or CC", "Void"))))

Add quotes to your numbers.

Formula is seeing them as text, not numbers.

Chuck
2001 Black Bullitt (For Sale)
2009 Black JK Unlimited X, M6, 35's
www.imboc.com
Rob (Tweakd) Memorial 3/13/05
Chuck is offline  
post #13 of 14 (permalink) Old 11-18-2009, 04:03 PM
Time Served
 
Join Date: Jul 2004
Location: Plano
Posts: 711
Easier formula for A3.

=IF(ISNUMBER(A3),TEXT(A3*100,"$0") & " - " & TEXT(A3*100+99,"$0"),"Void")

'01 Bullitt #0054

Last edited by bullitt54; 11-18-2009 at 04:08 PM. Reason: changed it to check for a number value
bullitt54 is offline  
post #14 of 14 (permalink) Old 11-18-2009, 04:32 PM Thread Starter
AHMO!
 
Join Date: Apr 2004
Location: the original let me google that for ya guy
Posts: 16,810
Quote:
Originally Posted by Chuck View Post
=IF(A6="1","Cash", IF(A6="2", "Check", IF(A6="3", "Multiple", IF(A6="4","MO or CC", "Void"))))

Add quotes to your numbers.

Formula is seeing them as text, not numbers.
Perfect...When I manually entered them as numbers it was working but when I used the MID it didn't like that.

Thanks Everyone.
Nate is offline  
Sponsored Links
Advertisement
 
Reply

Bookmarks

Quick Reply
Message:
Options

Register Now



In order to be able to post messages on the DFWstangs Forums forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.

User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.

Password:


Confirm Password:
Email Address
Please enter a valid email address for yourself.

Email Address:
OR

Log-in










Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page
Display Modes
Linear Mode Linear Mode



Posting Rules  
You may post new threads
You may 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 On
Trackbacks are On
Pingbacks are On
Refbacks are On

 
For the best viewing experience please update your browser to Google Chrome