Delete specific part of a cell run-time error 438 3

[vba317]

I am trying to delete a specific part of a cell with no luck. The cell is A3 on the sheet I am working on. The full string in cell A3 is JYC Internet                 (351) – includes. What I am trying to do is delete all characters after the - is this possible?

Tom

strChange = Range("A3").InStr(A3, "-")

 

[combo]

Range("A3")=Left(Range("A3"),Instr(Range("A3"),"-")

combo

 

[vba317]

I have tried this code but it deletes all characters in the cell. I did have to add another parenthses at the end to avoid a syntax error.

Range("A3") = Left(Range("A3"), InStr(Range("A3"), "-"))

 

[dhookom]

Are you sure there is always a "-" in the cell? If not, provide one:

Range("A3") = Left(Range("A3"), InStr(Range("A3") & "-", "-")-1)

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Use Data > Text to columns with DASH as delimiter.

 

[vba317]

Duane, I tried your idea and nothing gets deleted.
Skip, I tried your idea and it deletes all the info in the cell.

 

[PHV]

Double check the delimiter character in the cell.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

PHV means check the ASCII value of the character actually in the cell.

 

[dhookom]

VBA317,
Learn how to debug your code by setting breakpoints and debug.print statements.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

After using the mid statement to isolate the - and using the code statement on my spreadsheet the code is 150.

 

[vba317]

After adding the ascii value into combo's original code it works! Thanks you everyone for your input!

Range("A3") = Left(Range("A3"), InStr(Range("A3"), Chr(150)))

 

[Deniall]

FWIW, Char(150) is what is formally called an n-dash, which is usually printed as being slightly longer than the minus-sign character that is frequently used for a dash.[ ] For comparison, in the next line I will print a minus-sign, an n-dash, then an m-dash.
- [–] [—]
(An experienced eye can usually distinguish them fairly readily.)