CODEWITHSUNDEEP
excel
Jared Eitnier
Jared Eitnier

Reputation: 7152

If values in Column B = specific text, insert specific text into a value in Column A

Simply - if any cell in Column B contains thisvalue then append to the adjoining cell in Column A with sometext.

How is this done?

Upvotes: 0

Views: 68802

Answers (4)

jrad
jrad

Reputation: 3190

A simple if statement. For example:

=IF(ISNUMBER(SEARCH(thisvalue, B1)), sometext, "")

EDIT: The ISNUMBER(SEARCH(thisvalue, B1)) searches for thisvalue in B1, and if it finds it, it returns a number (that number being the starting index of thisvalue within B1).

EDIT #2: To append the inserted value to the end of the current value in cell A, use the CONCATENATE formula.

Example:

=CONCATENATE(A1, sometext)

Upvotes: 3

Ric
Ric

Reputation: 11

I think I have what you are looking for, let me know if you are still interested and if you want me to elaborate further. This formula in cell F2: =IF(ISNUMBER(SEARCH($U$2,E:E)),$V$2,"")&IF(ISNUMBER(SEARCH($U$3,E:E)),$V$3,"")&... where you are searching for a value that you specify in U2 across all cells in column E:E, if it finds a match it appends the value you specify in V2. To search for multiple words assigning corresponding value simply concatenate as shown as much as you like. I am able to specify hundreds of words (and corresponding values). I hope it helps.

Upvotes: 0

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

Put this formula in A1, then drag down as necessary:

=IF(B1="thisvalue","sometext","")

EDIT

Using a the Visual Basic Editor, you can update the contents of cell A like this:

Private Sub UpdateColumnA()
    Dim x As Long
    For x = 1 To 65536
        If InStr(1, Sheet1.Range("$B$" & x), "thisvalue") > 0 Then
            Sheet1.Range("$A$" & x) = Sheet1.Range("$A$" & x) & "sometext"
        End If
    Next
End Sub

Repeated runnings of the macro, however, will append the text again; you'll need more validation code if you don't want this to happen.

Upvotes: 1

Andrew
Andrew

Reputation: 7768

copy-paste in A1 , considering that you have values in B

=IF(ISNA(VLOOKUP("thisvalue",B:B,1,FALSE)),"",VLOOKUP("thisvalue",B:B,1,FALSE)&"ADDITIONAL VALUE")

it is saying: if value of vlookup is is empty (if lookup returns nothing) , then show empty value ( double quotes) but if the value of lookup returns something, then do this lookup and append "ADDITIONAL VALUE" text to found result

Upvotes: 0

Related Questions