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

On Monday, October 22, 2012 7:51:56 AM UTC-5, cphgeo wrote:
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









--

cphgeo

Look in the help index for FIND & SEARCH

Hi Nigel,

Your formula is looking in H20 for just "ZnS" and so doesn't fire the 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 the 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 given pattern to the "Descriptions"??? i.e. they always start with 3 characters and a comma and those are the only ones you're interested in...

Spencer,

thanks for the help.
below is a sample of several rows of data, the different decriptors are usually seperated by a comma. i have searched and replaced all the examples of '&'.
i wasnt sure if it would be useful to seperate the data using the data to columns function to leave just a single entry in each cell?? or perhaps there is a way to enable the formula to cope with multiple 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
nigel

Hi Nigel,

Sorry but I'm still a little confused...
Are the "descriptions" entered in a structured format or are they "free text". By that I mean are there rules that dictate what will be 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 way forward on this for you.

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

hi,

i have emailed you directly on this issue.
thanks

nigel