Remove first character then compare to another cell

**dleckie** · 07-11-2015, 01:18 AM

Hi all, new to this forum and forums in general, so I hope I posted my question correctly and that I am clear enough.

I have two columns where column "E" has just numbers that are 6-10 digits in length and the second column "H" starts with an alphanumeric character (lets say the letter B) then 6-10 digits after it.

The columns go from E1: E273 and H1:H273. (not always but long enough

)

I need to remove the first character in column H then compare the results to column E. If there is a different, I need to know and that can be up to your discretion, as long as I can see the results.. I could be notified by color, by putting a 0 or 1 in a different column, or by dropping the number in a any different column.

What I have tried is creating a blank column G and putting in it the formula
=REPLACE(H1,1,1,"")
which removed the first character in column H and showed up in column G

Then I put in blank column F the formula
=IF(ISNA(MATCH(G1,E:E,0)),G1,1)
which only copied the results from column G to column F as if to say they were different. If they were the same column F would have the results 1 and not the number.

Not sure what I did wrong. And if it can be done through a VBA macro, that would be fantastic. I will be using it a lot.

EX.

Colum E

14624720
7802049
24842259
28675186
32334880
30466791
2234169
24717605
45069143
7825783
16875105
13656190
9447593

Column H

B14624720
B7802049
B24842259
B28675186
B32334880
B30466791
J2234169
J24717605
J45069143
B7825783
B16875105
B13656190
B9447593

Thank you for your time and help,

Dave

**Admin** · 07-11-2015, 10:46 AM

**bakerman** · 07-12-2015, 04:28 AM

**Rick Rothstein** · 08-01-2015, 11:22 PM

Here is another macro you can consider...

Code:

Sub Test()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  Range("F1:F" & LastRow) = Evaluate(Replace("IF(E1:E#=0+MID(H1:H#,2,99),1,E1:E#)", "#", LastRow))
End Sub

**snb** · 08-11-2015, 02:14 PM

Or:

Code:

Sub M_snb()
   [I2:I200] = [if(E2:E200="","",if(E2:E200=abs(mid(H2:H200,2,100)),"x",""))]
End Sub

Thread: Remove first character then compare to another cell

Thread Tools

Search Thread

Display

Hybrid View

Remove first character then compare to another cell

Similar Threads

compare two tables

Remove Special Characters From Text Or Remove Numbers From Text

Macro To Delete Numbers With Trailing Character

compare column a to b and b to a

Count words having more than one character in a cell

Bookmarks

Bookmarks

Posting Permissions