help finding a formula for excell cell break down.

cblincoln43 · Mar 15th, 2002, 12:20 PM

I need a formula that breaks one cell down into three cells. On 3-13 I received a reply to a post that gave me the following.
cell I, = K L M
15.0 = 8.0, 4.0, 3.0
8.5 = 8.0, 0.5, False
8.0 = 8.0, Blank, False
0.0 = 0.0, Blank, False

What im looking for is is to break down cell I,into K, L,& M, as follows:

cell I K, L, M
15.0 = 8.0, 4.0, 3.0
8.5 = 8.0, 0.5, 0.0
8.0 = 8.0, 0.0, 0.0,(Or 0 = BLANK)
0.0 = 0.0, 0.0, 0.0
This is used to track time into deferent pay scales I = Total hours into, K = Reg.hours, L = Over time and M = Double time.

HELPPPPPP! MY 5LB HAMMER IS ABOUT YO DO COMPUTER VIOLANCE, TRYING TO FIGURE THIS ONE OUT. BOB.

[ This Message was edited by: cblincoln43 on 2002-03-15 11:22 ]

[ This Message was edited by: cblincoln43 on 2002-03-15 11:27 ]

Steve Hartman · Mar 15th, 2002, 12:25 PM

Have to try and help a man with a Hammer

What do you have in I?
Is it just the 15 or 15.0 = 8.0 4.0 3.0?

NateO · Mar 15th, 2002, 12:27 PM

It's fairly easy to get the data in two columns (drop the = sign and assume it) or get it into 5 columns.

Slect your data, click data (menu), text to columns....Click next. Either check space for 5 columns or check "other" and enter an = sign for 2 columns.

If you separate all values to their own columns (5), it'll be a lot easier to work with the data (e.g. =if(d1="Blank",0,d1)). Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-03-15 11:30 ]

cblincoln43 · Mar 15th, 2002, 12:29 PM

A range from 0.1 to say 20.1

cblincoln43 · Mar 15th, 2002, 12:37 PM

NatoO, I, would like to keep the columns open for use in other cells in a deferant catgories of info. in the same column.

NateO · Mar 15th, 2002, 01:28 PM

Not exactly sure I follow, but you can sperate the data by "=" as your delimeter. Then insert a column and fill it with = signs. Then it's in three columns as you originally asked. Also, all of this is macro recordable.

Also, there are 256 columns on a spreadsheet, 2 extra probably wouldn't hurt. Food for thought, you're the boss.

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-03-15 12:30 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 15th, 2002, 12:20 PM	#1
cblincoln43 Board Regular Join Date: Mar 2002 Posts: 204	I need a formula that breaks one cell down into three cells. On 3-13 I received a reply to a post that gave me the following. cell I, = K L M 15.0 = 8.0, 4.0, 3.0 8.5 = 8.0, 0.5, False 8.0 = 8.0, Blank, False 0.0 = 0.0, Blank, False What im looking for is is to break down cell I,into K, L,& M, as follows: cell I K, L, M 15.0 = 8.0, 4.0, 3.0 8.5 = 8.0, 0.5, 0.0 8.0 = 8.0, 0.0, 0.0,(Or 0 = BLANK) 0.0 = 0.0, 0.0, 0.0 This is used to track time into deferent pay scales I = Total hours into, K = Reg.hours, L = Over time and M = Double time. HELPPPPPP! MY 5LB HAMMER IS ABOUT YO DO COMPUTER VIOLANCE, TRYING TO FIGURE THIS ONE OUT. BOB. [ This Message was edited by: cblincoln43 on 2002-03-15 11:22 ] [ This Message was edited by: cblincoln43 on 2002-03-15 11:27 ]

Mar 15th, 2002, 12:25 PM	#2
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	Have to try and help a man with a Hammer What do you have in I? Is it just the 15 or 15.0 = 8.0 4.0 3.0?

Mar 15th, 2002, 12:27 PM	#3
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	It's fairly easy to get the data in two columns (drop the = sign and assume it) or get it into 5 columns. Slect your data, click data (menu), text to columns....Click next. Either check space for 5 columns or check "other" and enter an = sign for 2 columns. If you separate all values to their own columns (5), it'll be a lot easier to work with the data (e.g. =if(d1="Blank",0,d1)). Hope this helps. _________________ Cheers, NateO [ This Message was edited by: NateO on 2002-03-15 11:30 ]

Mar 15th, 2002, 12:29 PM	#4
cblincoln43 Board Regular Join Date: Mar 2002 Posts: 204	A range from 0.1 to say 20.1

Mar 15th, 2002, 12:37 PM	#5
cblincoln43 Board Regular Join Date: Mar 2002 Posts: 204	NatoO, I, would like to keep the columns open for use in other cells in a deferant catgories of info. in the same column.

Mar 15th, 2002, 01:28 PM	#6
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Not exactly sure I follow, but you can sperate the data by "=" as your delimeter. Then insert a column and fill it with = signs. Then it's in three columns as you originally asked. Also, all of this is macro recordable. Also, there are 256 columns on a spreadsheet, 2 extra probably wouldn't hurt. Food for thought, you're the boss. Hope this helps. _________________ Cheers, NateO [ This Message was edited by: NateO on 2002-03-15 12:30 ]