CODEWITHSUNDEEP
pythonvb.netexcelscripting
JustStartingOut
JustStartingOut

Reputation: 1

Insert blank column in Excel with values based on other column data

I have a spreadsheet that comes to me with a column that contains FQDN's of computers. However, filtering this is difficult because of the unique names and I ended up putting in a new column next the FQDN column and then entering a less unique value based on that name. An example of this would be:

dc01spmkt.domain.com new column value = "MARKETING"

All of the hosts will have a 3 letter designation so people can filter on the new column with the more generic titles.

My question is: Is there a way that I can script this so that when the raw sheet comes I can run the script and it will look for values in the old column to populate the new one? So if it finds 'mkt' together in the hostname field it writes MARKETING, or if it finds 'sls' it writes SALES?

Upvotes: 0

Views: 76

Answers (1)

C. Michael Boegner
C. Michael Boegner

Reputation: 21

If I understand you correctly, you should be able to do this with an if, isnumber, search formula as follows:

=IF(ISNUMBER(SEARCH("mkt",A1))=TRUE,"Marketing",IF(ISNUMBER(SEARCH("sls",A1))=TRUE,"Sales",""))

which would yield you the following:

asdfamkt    Marketing
sls         Sales
aj;sldkjfa  
a;sldkfja   
mkt         Marketing
sls         Sales

What this is doing is using Search, which returns the numbered place where your text you are searching begins in the field. Then you use ISNumber to return a true or false as to whether your Search returned a number, meaning it found your three letters in question. Then you are using the IF to say that if ISNumber is True, then you want to call it "Marketing" or whatever.

You can draw out the IF arguments for as many three letter variables as you would need to.

Hope this helped!

Upvotes: 2

Related Questions