coding descriptors

[cphgeo]

hi,
i have a spreadsheet with a description column containing multiple tex
entries (a short description of minerals).
i would like to extract and code the text into a new column.
an example of the data is

Description
ZnS, Zn2SiO4

i would like the function to then populate the new column with
ZM, ZBM

i have tried some IF functions but nothing happens!
here is one i tried
=if(H20="ZnS", ["ZM"])

when i enter this in the new code column it just enters that text in th
cell!
what should the new column be formatted for )text, number etc) and i
the formula correct?

thanks
nige

 

[Don Guillett]

hi,

i have a spreadsheet with a description column containing multiple text

entries (a short description of minerals).

i would like to extract and code the text into a new column.

an example of the data is



Description

ZnS, Zn2SiO4



i would like the function to then populate the new column with

ZM, ZBM



i have tried some IF functions but nothing happens!

here is one i tried

=if(H20="ZnS", ["ZM"])



when i enter this in the new code column it just enters that text in the

cell!

what should the new column be formatted for )text, number etc) and is

the formula correct?



thanks

nigel

Look in the help index for FIND & SEARCH

 

[Spencer101]

hi,
i have a spreadsheet with a description column containing multiple tex
entries (a short description of minerals).
i would like to extract and code the text into a new column.
an example of the data is

Description
ZnS, Zn2SiO4

i would like the function to then populate the new column with
ZM, ZBM

i have tried some IF functions but nothing happens!
here is one i tried
=if(H20="ZnS", ["ZM"])

when i enter this in the new code column it just enters that text in th
cell!
what should the new column be formatted for )text, number etc) and i
the formula correct?

thanks
nigel

Hi Nigel,

Your formula is looking in H20 for just "ZnS" and so doesn't fire th
TRUE condition of the IF formula if the cell contains "ZnS, Zn2SiO4".
It doesn't matter how you format the new column it will always fire th
FALSE condition of your formula if the text doesn't match exactly.

Could you give a sample of several rows of data and explain any give
pattern to the "Descriptions"??? i.e. they always start with
characters and a comma and those are the only ones you're intereste
in..

 

[cphgeo]

Hi Nigel,

Your formula is looking in H20 for just "ZnS" and so doesn't fire th
TRUE condition of the IF formula if the cell contains "ZnS, Zn2SiO4".
It doesn't matter how you format the new column it will always fire th
FALSE condition of your formula if the text doesn't match exactly.

Could you give a sample of several rows of data and explain any give
pattern to the "Descriptions"??? i.e. they always start with
characters and a comma and those are the only ones you're intereste
in...

Spencer,

thanks for the help.
below is a sample of several rows of data, the different decriptors ar
usually seperated by a comma. i have searched and replaced all th
examples of '&'.
i wasnt sure if it would be useful to seperate the data using the dat
to columns function to leave just a single entry in each cell?? o
perhaps there is a way to enable the formula to cope with multipl
entries?

from to lith description
25.50 27.50 RD Recrystallised Dolomite
27.50 29.20 RD Diss Zn
29.20 34.20 RD ZnS
34.20 42.70 RD Zn2SiO4, Fe
42.70 46.00 RD ZnS, PbS
0.00 7.90 RD Recrystallised Dolomite
7.90 11.00 RD Cavity, mud
11.00 15.50 RD Zn2SiO4, Dolomite
15.50 18.70 RD Diss Zn
18.70 28.30 RD Zn2SiO4
0.00 53.00 MGD Massive Grey Dolomite
53.00 87.00 RD Recrystallised Dolomite
0.00 6.40 RD Recrystallised Dolomite
6.40 7.00 RD Zn2SiO4
7.00 9.60 RD Recrystallised Dolomite

i hope that this helps.
regards
nige

 

[Spencer101]

Spencer

thanks for the help
below is a sample of several rows of data, the different decriptors ar
usually seperated by a comma. i have searched and replaced all th
examples of '&'
i wasnt sure if it would be useful to seperate the data using the dat
to columns function to leave just a single entry in each cell?? o
perhaps there is a way to enable the formula to cope with multipl
entries

from to lith descriptio
25.50 27.50 RD Recrystallised Dolomit
27.50 29.20 RD Diss Z
29.20 34.20 RD Zn
34.20 42.70 RD Zn2SiO4, F
42.70 46.00 RD ZnS, Pb
0.00 7.90 RD Recrystallised Dolomit
7.90 11.00 RD Cavity, mu
11.00 15.50 RD Zn2SiO4, Dolomit
15.50 18.70 RD Diss Z
18.70 28.30 RD Zn2SiO
0.00 53.00 MGD Massive Grey Dolomit
53.00 87.00 RD Recrystallised Dolomit
0.00 6.40 RD Recrystallised Dolomit
6.40 7.00 RD Zn2SiO
7.00 9.60 RD Recrystallised Dolomit

i hope that this helps
regard
nige

Hi Nigel

Sorry but I'm still a little confused..
Are the "descriptions" entered in a structured format or are they "fre
text". By that I mean are there rules that dictate what will b
recorded in that column or could it hold any text at all

The reason behind my question is I'm trying to figure the best wa
forward on this for you

Any chance of an actual Excel file with some examples of before an
after
Either posted here or emailed to me on pubnut @ gmail . com (withou
spaces

 

[cphgeo]

Hi Nigel,

Sorry but I'm still a little confused...
Are the "descriptions" entered in a structured format or are they "fre
text". By that I mean are there rules that dictate what will b
recorded in that column or could it hold any text at all?

The reason behind my question is I'm trying to figure the best wa
forward on this for you.

Any chance of an actual Excel file with some examples of before an
after?
Either posted here or emailed to me on pubnut @ gmail . com (withou
spaces)

hi,

i have emailed you directly on this issue.
thanks

nige