MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

checking strings

  Asked By: Lloyd    Date: Oct 17    Category: MS Office    Views: 732

I'm always trying to improve my spreadsheets and make my
job easier so I can impress my boss and everyone here helps a lot.
Thanks. Now I need some new information. How do I compare only the
first 10 charcters in a string? After that I need to check the fourth
character in the string to see if it is a "p".

I have this:

200P-701-BAD-01 200P701BAD01202874G 1 RH CAB DOOR ASSEMBLY
in two cells. I was going to eliminate all hyphens and then compare to
see if the first 10 characters are the same. Then I need to search the
first cell to determine if the fourth character is a "p". Thanks for
your help everyone.



6 Answers Found

Answer #1    Answered By: Blake Smith     Answered On: Oct 17

: 200P-701-BAD-01 200P701BAD01202874G 1 RH CAB DOOR ASSEMBLY
: in two cells. I was going to eliminate all hyphens and then
compare  to see if the first 10 characters  are the same. Then
: I need to search  the first cell  to determine  if the fourth
character  is a "p".

That sounds like a good plan. Which part are you having
trouble with?

Answer #2    Answered By: Ryder Anderson     Answered On: Oct 17

I don't know how to set up a fixed length string. Until now I just
compared cells. Basically I need to know how to read in a 10 character
string and then be able to check  the 4th character

Answer #3    Answered By: Angelica Ramos     Answered On: Oct 17

Lookup the help  for "left" and "mid".

firstTen_A = Left(str, 10) should give you the first ten chars ...
if ( mid(str, 4, 1) = "P" ) then ... will allow you to check  the 4th char.

Not used VB/VBA much recently and the syntax may be incorrect. Read the

Answer #4    Answered By: Lonnie Rogers     Answered On: Oct 17

Try this: Put the string  with dashes in cell  B2, and the string
without dashes in cell C2.

Then paste the following code into the code window for ThisWorkbook:
Option Explicit

Public Sub compareTwoStrings()
'Compare the string in cell B2 with the string in cell B3.
'The first two dashes in cell B2 are to be ignored.
'Assume cell B2 is a consistent pattern of 3 substrings
' of length 4, 3 and 3 separated by two dashes.
'Thus the string in cell B2 has length = 12.
'Likewise cell B3 has length = 10.
Dim strFirst As String, strSecond As String
strFirst = Left(Range("$B$2"), 12)
strSecond = Left(Range("$C$2"), 10)
'The split will return an array of the first 3 substrings.
Dim arrSplit
arrSplit = Split(strFirst, Chr(45), 3, vbTextCompare)
Dim ctr As Integer
're-initialize strFirst since arrSplit is storing the substrings.
strFirst = ""
For ctr = 0 To 2 'arrSplit is always zero-based.
'This line concatenates the 3 substrings without the dashes.
strFirst = strFirst & arrSplit(ctr)
'Now compare  the two strings.
Dim intResult As Integer
intResult = StrComp(strFirst, strSecond, vbTextCompare)
If intResult = 0 Then
MsgBox "Comparison exact."
MsgBox "They don't match."
End If
End Sub

Answer #5    Answered By: Hubba Akhtar     Answered On: Oct 17

Try this
C2 cell  is our Search
B2 cell is our Text where we search

Answer #6    Answered By: Sumitra 2004     Answered On: Oct 17

Simply use a MID statement...

Dim sHold As String

sHold = Mid("Rapper", 4, 1)

If sHold = "p" Then

'Do something here


'Don't worry about it

End If

Didn't find what you were looking for? Find more on checking strings Or get search suggestion and latest updates.