If column A contains then add B

[Dharmalingam]

Hi,

Could any one pls help me here, im having sheet with below values in column A (it contains n number of values)

<clientname>RBS</clientname>

<customerid>98</customerid>

<customerreference>H12</customerreference>

<customerid>A34</customerid>

<customerreference>WR2</customerreference>

<clientname>TSQ</clientname>

<clientcode></clientcode>

<clientcontact>98765432<clientcontact></clientcontact></clientcontact>

<clientname></clientname>

<tbody>
</tbody>

If Column A contains the word Clientname then i need to add 1 into column B, in same way if Column A contains the word CustomerId then i need to add 2 into its respective Column B, like below could any one pls.

<clientname>RBS</clientname> <tbody> </tbody>	1
<customerid>98</customerid> <tbody> </tbody>	2
<customerreference>H12</customerreference> <tbody> </tbody>
<customerid>A34</customerid> <tbody> </tbody>	2
<customerreference>WR2</customerreference> <tbody> </tbody>
<clientname>TSQ</clientname> <tbody> </tbody>	1
<clientcode></clientcode> <tbody> </tbody>
<clientcontact>98765432<clientcontact></clientcontact></clientcontact> <tbody> </tbody>
<clientname></clientname> <tbody> </tbody>	1

<tbody>
</tbody>

Thank you very much for your help.

Thanks,
Dharma.

 

[Marcelo Branco]

Try

B1
=IF(ISNUMBER(SEARCH("clientname",A1)),1,IF(ISNUMBER(SEARCH("customerid",A1)),2,""))
copy down

M.

 

[Dharmalingam]

Brilliant, Thanks Marcelo it works...

 

[Marcelo Branco]

Brilliant, Thanks Marcelo it works...

You are welcome and thanks for the feedback.

M.