copying information between Commas?

[Pivotrend]

14-Apr-04,1.38,1.44,1.31,1.34,3129800,1.34
i have this information in Cell A1


i need Cell B1 to copy the begining only like: 14-Apr-04
and C1 to copy 1.38
and D1 1.44
and E1 1.31
and F1 1.34
and G1 3129800

whats the formula that gets information between commas

 

[Gord Dibben]

Pivot

Try Data>Text to Columns>De-limited>Next>Comma>Finish.

Gord Dibben Excel MVP

 

[Pivotrend]

thanx Dibben

but i want to do this without using Delimite

 

[Pivotrend]

thanx Dibben

but i want to do this without using Delimite

 

[Pivotrend]

thanx Dibben

but i want to do this without using Delimiter

any ideas

 

[Gord Dibben]

Don't understand your request. Do you want it done by a formula written in
each of B1, C1, D1 and E1?

Text to Columns comma de-limited will break up your cell data as per your
example.

If you want something else to appear, please show another example.

Gord Dibben Excel MVP

 

[Pivotrend]

yes Dibben
that's what i ment, a formula in each cell :

 

[Pivotrend]

yes Dibben
that's what i ment, a formula in each cell

without using delimite

 

[RagDyeR]

You keep repeating, <<"without using delimiter">>

Do you mean that you want the data displayed in the new columns *without*
the *commas*,
OR
Do you mean that you want the data displayed in the new columns *without*
the *decimals (periods)*?

If you tried Gord's suggestion, you would see that the data is displayed in
the new columns without *commas*.

Do you want the *decimals eliminated* also?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


yes Dibben
that's what i ment, a formula in each cell

without using delimiter

 

[Pivotrend]

yeR

it's easy
i need to copy information between commas

it can't be easier than this to understand!!

 

[Gord Dibben]

Did you try Text to Columns with comma de-limited?

What were the results?

Gord

 

[Pivotrend]

Dibben

the result is spliting all digits between commas to other columns

i know the result & you know the result
but like i said
i need a formula to copy information between commas
not by using delimite

 

[Pivotrend]

Dibben

the result is spliting all digits between commas to other columns

i know the result & you know the result
but like i said
i need a formula to copy information between commas
not by using delimite

 

[Gord Dibben]

Perhaps the info, formulas and Functions on Chip Pearson's First/Last names
page will help you to build formulas to Find the commas and extract the data
between them.

Lotsa work compared to Text to columns!!

Gord

 

[Ragdyer]

Hey Gord,

According to the OP, *you* know the result.<g>

How about just submitting this, since it works perfectly on the posted
example, and gives the result that *you* know about.


This will take the *submitted example*, and extract the data between the
commas, and display it in individual columns:

In B1:
=LEFT(A1,FIND(",",A1)-1)

In C1:
=MID(A1,FIND(",",A1)+1,4)

In D1:
=MID(A1,FIND(",",A1,LEN(B1)+3)+1,4)

In E1:
=MID(A1,FIND(",",A1,LEN(B1)+LEN(C1)+3)+1,4)

In F1:
=MID(A1,FIND(",",A1,LEN(B1)+LEN(C1)+LEN(D1)+5)+1,4)

In G1:
=MID(A1,FIND(",",A1,LEN(B1)+LEN(C1)+LEN(D1)+LEN(E1)+7)+1,7)

In H1:
=RIGHT(A1,4)

 

[Pivotrend]

Ragdyer

problem with your formula is that it copies fields behind commas no
between them
& in the formula you typed you decided how many fields you wanted t
copy behind commas

those fields between commas change all the time
figures change all the time

was a nice try anyway

any more ideas

 

[Debra Dalgleish]

To determine the length of the string, you can calculate the position of
the next comma, and subtract the position of the previous one, e.g.:


=MID(A1,SEARCH(",",A1)+1,SEARCH(",",A1,SEARCH(",",A1)+1)-SEARCH(",",A1)-1)

 

[Pivotrend]

hi Deb

how do i enter this information in each cell?

 

[AlfD]

Hi!

I'll assume that there are 7 sets of data in between commas in A1.

Put =LEFT(A1,FIND(",",A1)-1) in B1
Put =RIGHT(A1,LEN(A1)-LEN(B1)-1) in C1

Copy B1 and C1 and paste this into D1,F1,H1,J1,L1

Hide columns C,E,G,I,K.

Columns B,D,F,H,J,L,M will show the separate pieces of the data.

Now I guess you'll come back and tell me you can't put extra columns
in...

Next issue: you raised the possibility that there might not be 7 such
pieces of data every time. This particular example will throw #VALUE
errors if there are no more commas to be found (e.g. with only 6 items
present). You would need to put in error traps to catch this sort of
eventuality. It will, however, cope if there is a blank item (comma
followed by comma).

If you know you will ge more than 7 items, then you will need to have
pairs of columns to match your maximum count anyway.

Alf

Alf

 

[Pivotrend]

W0000000000000000W A-l-f-D-


that did it
Finally

Than