Trying to Locate position of a DASH "-" in Cell or String

[tbroniecki]

I have a Cell A1 which contains the following 12536485-01. I would like to identify the location of the dash in the string. When I use =SEARCH("-", A1,3) in EXCEL it returns 8, which is what I would like. What I am trying to do in the code is to seperate either side of the dash and concatenate the two parts without the dash. In short I want to remove the dash from my strings. Maybe there's a better way of doing this???

Can Anyone Help..

This code doesn't do what is required....

vLocnDash = Search("-", (Cells(vrow, 1)), 3)
vLenProdId = Len(Cells(vrow, 1))
vLenSecondPt = vLenprodId - vLocnDash
' If Dashes found
If vLenSecondPt > 0 Then
vLenFirstPt = vLocnDash - 1
FirstPart = Left((Cells(vrow, 1)), vLenFirstPt)
SecondPart = Right((Cells(vrow, 1)), vLenSecondPart)
Cells(vrow, 1) = (FirstPart & SecondPart)
End If



This is how I changed the code to make it do what I needed.

This Code removes a "-" from a String in a Cell and rejoins the parts on either side of the dash to return a value without the dash. Can be used to remove any character in a string. e.g. takes someting like this 90289374-01 and returns 9028937401. Note this process only removes one dash. Can bve modified to remove more dashes if reqiuired.

' Returns the position number of the dash in a string
vLocnDash = InStr(1, (Cells(vrow, 1)), "-", vbTextCompare)
' Establish the length of the string
vLenprodId = Len(Cells(vrow, 1))
' Calculate the number of postions right of the dash
vLenSecondPt = vLenprodId - vLocnDash
' If Dashes found (vLocnDash is zero when no dash is found)
If vLocnDash > 0 Then
' Establishing the number of positions left of the dash
vLenFirstPt = vLocnDash - 1
' Storing the characters left of the dash in FirstPart String
FirstPart = Left((Cells(vrow, 1)), vLenFirstPt)
' Storing the characters right of the dash in SecondPart string
SecondPart = Right((Cells(vrow, 1)), vLenSecondPt)
' Concatenating Firstpart & SecondPart to recreate the string without a dash
Cells(vrow, 1) = (FirstPart & SecondPart)
End If

This was how I was able to achieve what I needed.

Ted Broniecki answering his own question once again....

 

[tbroniecki]

DashPos = InStr(1, (Cells(vrow,1)), "-", vbTextCompare)


This identifies a Dash in the eighth position and returns the value 8.

Ted to Ted once again....