Text to Column not Working As Intended

[TeckTeck]

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

 

[Legacy 143009]

Hi,

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.

 

[TeckTeck]

Hi,

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.

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 disappears

 

[Legacy 143009]

Ok,

First, find the asci code of that character for only once:

Msgbox Asc(Mid(Cells(5, "M").Value, 4, 1))

Then write a macro replaces this ascii code with dashes like this:

For i = 5 To 100
  vNewText = Cells(i, "M").Value
  Cells(i, "M").Value = WorksheetFunction.Substitute(vNewText, Chr(yourAsciiCode), "")
Next

 

[TeckTeck]

Ok,

First, find the asci code of that character for only once:

Msgbox Asc(Mid(Cells(5, "M").Value, 4, 1))

Then write a macro replaces this ascii code with dashes like this:

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 savior

 

[Legacy 143009]

Oh I forgot the dash but you've figured it out I guess

 

[TeckTeck]

Oh I forgot the dash but you've figured it out I guess

Yess ive figured it out, :D