Split strange symbol (like strikethrough) out of text.

[S.H.A.D.O.]

Good evening,

I have a long list that looks like this:

[TABLE="width: 447"]
<tbody>[TR]
[TD]VPN—Virtual Private Network
[/TD]
[/TR]
[TR]
[TD]VPU—Visual Processing Unit
[/TD]
[/TR]
[TR]
[TD]WAN—Wide Area Network
[/TD]
[/TR]
[TR]
[TD]WAP—Wireless Access Point
[/TD]
[/TR]
[TR]
[TD]WAP—Wireless Application Protocol
[/TD]
[/TR]
[TR]
[TD]WINS—Windows Internet Name Service
[/TD]
[/TR]
[TR]
[TD]WLAN—Wireless Local Area Network

I want to split each cell into two columns, basically without what looks like the extended minus sign, hyphen. I have tried text-to-columns and formulas such as...

=LEFT(B738,FIND("-",B738)-1)
=RIGHT(B738,LEN(B738)-FIND("-",B738))

... but to no avail.

Any help will be greatly appreciated.

Thanks in advance.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Orionator]

Did you try to copy the character and paste it into the find formula?

 

[S.H.A.D.O.]

Brilliant, thanks Orionator, that worked perfectly.

I think I got to the stage where I couldn't see the wood for the trees.

Thanks again.

 

[Orionator]

In cases like this, I LOVE the KISS method. And then I often find myself face-palming when I realize what I've done.

 

[jtakw]

Hi,

That's an "em dash", glad you got it sorted out, you can also try these,

A single formula for both columns, B1 formula copied down and across to C7
Separate formulas for each column, E1 and F1 formulas copied down.

Excel 2010
	A	B	C	D	E	F
1	VPNVirtual Private Network	VPN	Virtual Private Network		VPN	Virtual Private Network
2	VPUVisual Processing Unit	VPU	Visual Processing Unit		VPU	Visual Processing Unit
3	WANWide Area Network	WAN	Wide Area Network		WAN	Wide Area Network
4	WAPWireless Access Point	WAP	Wireless Access Point		WAP	Wireless Access Point
5	WAPWireless Application Protocol	WAP	Wireless Application Protocol		WAP	Wireless Application Protocol
6	WINSWindows Internet Name Service	WINS	Windows Internet Name Service		WINS	Windows Internet Name Service
7	WLANWireless Local Area Network	WLAN	Wireless Local Area Network		WLAN	Wireless Local Area Network
Sheet30

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE($A1,CHAR(151),REPT(" ",100)),COLUMNS($B1:B1)*100-99,100))
E1	=LEFT(A1,FIND(CHAR(151),A1)-1)
F1	=MID(A1,FIND(CHAR(151),A1)+1,255)

 

[S.H.A.D.O.]

Thanks for the formulas and explanations jtakw, it is appreciated.

The first formula works the best because some of the cells had a space at the beggining. With the first formula it took out those spaces so I did not have to TRIM the results.

The second formula also worked but I would have to TRIM the results.

Thanks again.

 

[jtakw]

You're welcome, glad it was useful.