CODEWITHSUNDEEP
excelvba
James Wilson
James Wilson

Reputation: 21

excel find string and then IF statement it

I have basically inherited a database within which the data is pretty messed up. I need to start cleansing it which I am doing in Excel.

The data has a code which has then been used within the other cells when it should not have. Basically I want to write some VB which will look at CellB and compare the first section of text between [ and . with the text found in CellA.

If they are the same i want it to remove the text from CellB if it is not I want it to just leave CellB as is.

The example below is the simplest I need to do this in loads of columns that have loads of different sections of text the only constant is that the code I need to check will be between the first [ and . in all columns.

I have tried writing some VB using MID statement but can’t get the hang of it.

Any ideas?

Before
A            B
M548    [M548.PLANNED]
M513    [M503.PLANNED]
M503    [M503.PLANNED]

After
A            B
M548    [.PLANNED]
M513    [M503.PLANNED]
M503    [.PLANNED]

Upvotes: 0

Views: 156

Answers (3)

togatown
togatown

Reputation: 33

A simple replace would work if the value in A only occurs once in B:

Sub cleanData()
    For i = 1 To 3
        Cells(i, 2).Value = Replace(Cells(i, 2).Value, Cells(i, 1).Value, "")
    Next
End Sub

Upvotes: 0

James
James

Reputation: 209

If every piece of column B's data is in that format, this could work:

=IF(MID(B4,2,4)=A4,"[.PLANNED]",B4)

Here's a picture for added clarification:

Image Link 1

You would then 'Paste Values' into the second column to replace the current data.

Upvotes: 2

Ramana Viswanadha
Ramana Viswanadha

Reputation: 179

If your data is in the same exact format, as in [ followed by 4 characters you can just use the following formula in a Column C. 

=IF(MID(B2,2,4)=A2,CONCATENATE("[",MID(B2,SEARCH(".",B2),100)),B2)

Upvotes: 0

Related Questions