Hi ive been trying to figure out why this text to column doesn't work, it seems like there is something to do with the formatting of "-". Although they look the same but i cant get them separated using Text to column. Please advice
Hi Flashbond, yes this would work however it would affect my vba code downstream. Hopefully i am able to have a vba code to replace the problematic dashes with a standard dash. Another thing that i noticed is that if i try to copy the excel cell value into a word document, the hypens disappearsHi,
If I were you, I would copy the problematic character, go to Find&Replace and replace all with a standard dash.
Not a real solution but maysolve this particular issue.
Msgbox Asc(Mid(Cells(5, "M").Value, 4, 1))
For i = 5 To 100
vNewText = Cells(i, "M").Value
Cells(i, "M").Value = WorksheetFunction.Substitute(vNewText, Chr(yourAsciiCode), "")
Next
This works perfectly, thank you Flashbond you're a saviorOk,
First, find the asci code of that character for only once:
Then write a macro replaces this ascii code with dashes like this:VBA Code:Msgbox Asc(Mid(Cells(5, "M").Value, 4, 1))
VBA Code:For i = 5 To 100 vNewText = Cells(i, "M").Value Cells(i, "M").Value = WorksheetFunction.Substitute(vNewText, Chr(yourAsciiCode), "") Next
Yess ive figured it out, :DOh I forgot the dash but you've figured it out I guess