Help with disappearing dash

[marketingneedsML]

Hello all!

I'm compiling data for a website. In the description you can see that after "RD" or "KP" there is a dash (i.e. 14KP-2.67). However, in the formula bar, those dashes don't appear and it's been messing up my formula. Is there any way to bypass this and would anyone know why this is happening? Appreciate any comments or suggestions. Thank you!

 

[Saurabhj]

Hi, Welcome to Mr. Excel board.

Is the website also showing dash?

If you use below code what is the ascii code shown for dash ? Please share screenshot.
Great, if you can share the dataset using XL2BB.

Sub showasc()
Dim word As String, wordlength As Integer
Dim char As Integer
word = ActiveSheet.Range("H639")

wordlength = Len(word)

For char = 1 To wordlength
    MsgBox Mid(word, char, 1) & vbTab & Asc(Mid(word, char, 1))
Next

End Sub

 

[marketingneedsML]

Hi! Thank you so much for the response. Unfortunately, it is not me who's making the website so I wouldn't know what the code is for the website. Here's the mini-sheet from my excel file.

productmapping.xlsx
	A		C	D				H										R	S	T	U
303	N1254PAMP		$331	PENDANTS				14KP-1.3GR. 14KP2.67GR. 40RD.12 1PAM2.88										1.3	2.6
productMapping

Cell Formulas
Range	Formula
R303	R303	=(IF($D303="PENDANTS", IF(ISNUMBER(SEARCH("KW/Y/P",$H303)),(MID($H303,SEARCH("K",$H303)+7,SEARCH("G",$H303)-SEARCH("K",$H303)-7)), IF(ISNUMBER(SEARCH("KW",$H303)),(MID($H303,SEARCH("K",$H303)+3,SEARCH("G",$H303)-SEARCH("K",$H303)-3)), IF(ISNUMBER(SEARCH("KY/P",$H303)),(MID($H303,SEARCH("K",$H303)+5,SEARCH("G",$H303)-SEARCH("K",$H303)-5)), IF(ISNUMBER(SEARCH("KW/P",$H303)),(MID($H303,SEARCH("K",$H303)+5,SEARCH("G",$H303)-SEARCH("K",$H303)-5)), IF(ISNUMBER(SEARCH("KW/Y",$H303)),(MID($H303,SEARCH("K",$H303)+5,SEARCH("G",$H303)-SEARCH("K",$H303)-5)), (MID($H303,SEARCH("K",$H303)+3,SEARCH("GR",$H303)-SEARCH("K",$H303)-3))))))),""))
S303	S303	=IFERROR(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(IF( ISNUMBER(SEARCH("K",$H303)), (IF($D303="PENDANTS", IF(ISNUMBER(SEARCH("KW/Y/P",$H303)),(MID($H303,SEARCH("K",$H303)+19,SEARCH("G",$H303)-SEARCH("K",$H303)-7)), IF(ISNUMBER(SEARCH("KY/P",$H303)),(MID($H303,SEARCH("K",$H303)+19,SEARCH("G",$H303)-SEARCH("K",$H303)-5)), IF(ISNUMBER(SEARCH("KW/P",$H303)),(MID($H303,SEARCH("K",$H303)+17,SEARCH("G",$H303)-SEARCH("K",$H303)-2)), IF(ISNUMBER(SEARCH("KW/Y",$H303)),(MID($H303,SEARCH("K",$H303)+17,SEARCH("G",$H303)-SEARCH("K",$H303)-2)), (MID($H303,SEARCH("K",$H303)+15,SEARCH("GR",$H303)-SEARCH("K",$H303)-3)))))), (MID($H303,SEARCH("K",$H303)+3,SEARCH("G",$H303)-SEARCH("K",$H303)-3)))),""), "/",""), "-",""), "*",""), "Y",""), "P",""),"")
T303	T303	=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(IF( ISNUMBER(SEARCH("RD-",$H303)),MID($H303,FIND("RD-",$H303)+3,4)," "), "*",""), "(",""), "C",""), "W","")
U303	U303	=IFERROR(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(IF( ISNUMBER(SEARCH("BT",$H303)),MID($H303,FIND("BT-",$H303)+3,4), IF(ISNUMBER(SEARCH("SA",$H303)),MID($H303,FIND("SA-",$H303)+3,4), IF(ISNUMBER(SEARCH("CT",$H303)),MID($H303,FIND("CT-",$H303)+3,4), IF(ISNUMBER(SEARCH("RU",$H303)),MID($H303,FIND("RU-",$H303)+3,4), IF(ISNUMBER(SEARCH("TQ",$H303)),MID($H303,FIND("TQ-",$H303)+3,4), IF(ISNUMBER(SEARCH("SQ",$H303)),MID($H303,FIND("SQ-",$H303)+3,4), IF(ISNUMBER(SEARCH("MOP",$H303)),MID($H303,FIND("MOP-",$H303)+4,4), IF(ISNUMBER(SEARCH("GAG",$H303)),MID($H303,FIND("GAG-",$H303)+4,4), IF(ISNUMBER(SEARCH("BO",$H303)),MID($H303,FIND("BO-",$H303)+3,4), IF(ISNUMBER(SEARCH("CMG",$H303)),MID($H303,FIND("CMG-",$H303)+4,4), IF(ISNUMBER(SEARCH("LAP",$H303)),MID($H303,FIND("LAP-",$H303)+4,4), IF(ISNUMBER(SEARCH("AM",$H303)),MID($H303,FIND("AM-",$H303)+3,4), IF(ISNUMBER(SEARCH("AQ",$H303)),MID($H303,FIND("AQ-",$H303)+3,4), IF(ISNUMBER(SEARCH("EM",$H303)),MID($H303,FIND("EM-",$H303)+3,4), IF(ISNUMBER(SEARCH("GAM",$H303)),MID($H303,FIND("GAM-",$H303)+4,4), IF(ISNUMBER(SEARCH("GA",$H303)),MID($H303,FIND("GA-",$H303)+3,4), IF(ISNUMBER(SEARCH("GG",$H303)),MID($H303,FIND("GG-",$H303)+3,4), IF(ISNUMBER(SEARCH("GTR",$H303)),MID($H303,FIND("GTR-",$H303)+4,4), IF(ISNUMBER(SEARCH("LAD",$H303)),MID($H303,FIND("LAD-",$H303)+4,4), IF(ISNUMBER(SEARCH("LBT",$H303)),MID($H303,FIND("LBT-",$H303)+4,4), IF(ISNUMBER(SEARCH("LMT",$H303)),MID($H303,FIND("LMT-",$H303)+4,4), IF(ISNUMBER(SEARCH("MAL",$H303)),MID($H303,FIND("MAL-",$H303)+4,4), IF(ISNUMBER(SEARCH("MCSA",$H303)),MID($H303,FIND("MCSA-",$H303)+5,4), IF(ISNUMBER(SEARCH("OP",$H303)),MID($H303,FIND("OP-",$H303)+3,4), IF(ISNUMBER(SEARCH("PE",$H303)),MID($H303,FIND("PE-",$H303)+3,4), IF(ISNUMBER(SEARCH("PAM",$H303)),MID($H303,FIND("PAM-",$H303)+4,4), IF(ISNUMBER(SEARCH("PMOP",$H303)),MID($H303,FIND("PMOP-",$H303)+5,4), IF(ISNUMBER(SEARCH("PS",$H303)),MID($H303,FIND("PS-",$H303)+3,4), IF(ISNUMBER(SEARCH("ST",$H303)),MID($H303,FIND("ST-",$H303)+3,4), IF(ISNUMBER(SEARCH("PTR",$H303)),MID($H303,FIND("PTR-",$H303)+4,4), IF(ISNUMBER(SEARCH("SMT",$H303)),MID($H303,FIND("SMT-",$H303)+4,4), IF(ISNUMBER(SEARCH("SWBT",$H303)),MID($H303,FIND("SWBT-",$H303)+5,4), IF(ISNUMBER(SEARCH("WT",$H303)),MID($H303,FIND("WT-",$H303)+3,4), IF(ISNUMBER(SEARCH("CAX",$H303)),MID($H303,FIND("CAX-",$H303)+4,4), IF(ISNUMBER(SEARCH("CBC",$H303)),MID($H303,FIND("CBC-",$H303)+4,4), IF(ISNUMBER(SEARCH("CPAR",$H303)),MID($H303,FIND("CPAR-",$H303)+5,4), IF(ISNUMBER(SEARCH("CGC",$H303)),MID($H303,FIND("CGC-",$H303)+4,4), IF(ISNUMBER(SEARCH("CGS",$H303)),MID($H303,FIND("CGS-",$H303)+4,4), IF(ISNUMBER(SEARCH("COC",$H303)),MID($H303,FIND("COC-",$H303)+4,4), IF(ISNUMBER(SEARCH("TRQ",$H303)),MID($H303,FIND("TRQ-",$H303)+4,4), IF(ISNUMBER(SEARCH("CPC",$H303)),MID($H303,FIND("CPC-",$H303)+4,4), IF(ISNUMBER(SEARCH("RAG",$H303)),MID($H303,FIND("RAG-",$H303)+4,4), IF(ISNUMBER(SEARCH("AX",$H303)),MID($H303,FIND("AX-",$H303)+3,4), IF(ISNUMBER(SEARCH("CT",$H303)),MID($H303,FIND("CT-",$H303)+3,4), IF(ISNUMBER(SEARCH("BC",$H303)),MID($H303,FIND("BC-",$H303)+3,4), IF(ISNUMBER(SEARCH("GC",$H303)),MID($H303,FIND("GC-",$H303)+3,4), IF(ISNUMBER(SEARCH("CRC",$H303)),MID($H303,FIND("CRC-",$H303)+4,4)," "))))))))))))))))))))))))))))))))))))))))))))))), "*",""), "(",""), "G",""), "x",""),"")