Seperating data from one cell to many within one record

  • Thread starter Thread starter Simon Clark
  • Start date Start date
S

Simon Clark

Hi, i need some help with seperating data from one cell to many. I
have searched this data very well and im sure someone will say "Use
Text to Columns", well it doesnt work.

Ok, i have the following as an example...

-----------------------------------------------------------------
Country | DatesTimes | Names | Log |
---------|--------------------|-------|---------------|----------
Nordix | 30.04.2004 |Simon | Order Received|
| 05.05.2004 14:33 |Cath | Submitted |
| 05.05.2004 15:16 |Simon | Created |
-----------------------------------------------------------------
Nordix |and then more records a like.......
-----------------------------------------------------------------

What i am after is a VBA function that will seperate the "DatesTimes"
and the other multiple data cells to be seperated as indivual cells,
but keeping them intact with the actual record. So it looks like
this....;

-----------------------------------------------------------------
Country | DatesTimes | Names | Log |
---------|--------------------|-------|---------------|----------
Nordix | 30.04.2004 |Simon | Order Received|
|-------------------- ------- ---------------
| 05.05.2004 14:33 |Cath | Submitted |
|-------------------- ------- ---------------
| 05.05.2004 15:16 |Simon | Created |
-----------------------------------------------------------------
Nordix |etc......
-----------------------------------------------------------------

Can this be acheived at all?
Cheers guys...

Simon
 
Howdie Simon,
You need the "TEXT" functions.
I see that at times you have a country at others not.
This is my routine tested that either prints country in a cell or
blank:
Starting on the leftmost column1 find the first " point"
If location of this is>3 you have country code,else:"BLANK".
If you have country code then it extends from column1-column(point)-4.

The followintg formula writes the country name into a cell or a blan
if not entered

if(find(".",a1,1)>3,left(a1,find(".",a1)-4),"")

To get the date, search again for first "point" and if it is i
column:n then your date runs from column: n-2 to n+7 (?)

You need a MID function to copy text from cell A1 with a given startin
column number and number characters.


I hope this helps, if not writwe to me

Gabo
 
Gabor Sebo said:
Howdie Simon,
You need the "TEXT" functions.
I see that at times you have a country at others not.
This is my routine tested that either prints country in a cell or a
blank:
Starting on the leftmost column1 find the first " point"
If location of this is>3 you have country code,else:"BLANK".
If you have country code then it extends from column1-column(point)-4.

The followintg formula writes the country name into a cell or a blank
if not entered

if(find(".",a1,1)>3,left(a1,find(".",a1)-4),"")

To get the date, search again for first "point" and if it is in
column:n then your date runs from column: n-2 to n+7 (?)

You need a MID function to copy text from cell A1 with a given starting
column number and number characters.


I hope this helps, if not writwe to me

Gabor
Click to expand...


Cheers for that. I see what you are doing, but unfortunately, i am a
complete novice at VBA and excel functions. Is this simply a formula
command or a VBA function that i need to include somewhere within VBA.

Thanks for looking at this.

Simon
 
Back
Top