Text String Compare

rainmantwo

New Member
Joined
Jun 13, 2002
Messages
45
Is there any way to compare the first 5 characters of ce;l a2 with the frist frive chararcters of c2 and then write a true of false?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,951
Office Version
  1. 365
Platform
  1. Windows
Simply use:

=Left(A2,5)=Left(C2,5)

It will return TRUE or FALSE
 

rainmantwo

New Member
Joined
Jun 13, 2002
Messages
45
Can I add a delete to this

if the charatcers in c2 match the ones in a2 can you delete the c2 contents?

Thanks that was quick
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,951
Office Version
  1. 365
Platform
  1. Windows
Now with an Excel formula. Excel formulas cannot delete values in other cells. You would need to use VBA to do that, i.e.

Code:
If Left(Range("A2"),5)=Left(Range("C2"),5) Then Range("C2").ClearContents
 

rainmantwo

New Member
Joined
Jun 13, 2002
Messages
45

ADVERTISEMENT

Thanks for the help !! But...

Thanks,

It seemes to work fine in row 2 but I need the marco to look at all the rows.. sometimes 8,000.

Thanks again
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,951
Office Version
  1. 365
Platform
  1. Windows
Then you need to write a loop to loop through the range, i.e.

Code:
Dim i as Long
For i = 2 to 8000
   If Left(Cells(i,"A"),5)=Left(Cells(i,"C"),5) Then Cells(i,"C").ClearContents
Next i

Edited to fix code.
 

rainmantwo

New Member
Joined
Jun 13, 2002
Messages
45

ADVERTISEMENT

loop through

sorry, new to vb I do not know how to write a loop through
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,951
Office Version
  1. 365
Platform
  1. Windows
sorry, new to vb I do not know how to write a loop through
???
I wrote it for you in my previous post...
 

BJungheim

Well-known Member
Joined
Jul 24, 2004
Messages
914
Then you need to write a loop to loop through the range, i.e.

Code:
Dim i as Long
For i = 2 to 8000
   If Left(Cells(i,"A"),5)=Left(Cells(i,"C"),5) Then Cells(i,"C").ClearContents
End If

You didn't close the loop:
Code:
Dim i as Long
For i = 2 to 8000
   If Left(Cells(i,"A"),5)=Left(Cells(i,"C"),5) Then Cells(i,"C").ClearContents
Next
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,951
Office Version
  1. 365
Platform
  1. Windows
My apologies, you are correct BJungheim.

I meant to type in "Next i" instead of "End If". I went back and re-editing the code.

I guess that is what I get for trying to write code at 11:00 PM when I should be in bed!
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top