Imported wrap text

[normpam]

Very interesting.

The data is imported from another source like this:

48 Limestone St.Markham, ON L6B0P6 (no space after St.)

When applying Wrap Text it shows up as:
48 Limestone St.
Markham, ON L6BOP6

When I use =Left(cell, 18) or =left(cell,17) I note that the result is the same - 48 Limestone St.

Apparently there is some type of character that is in Excel when the data was imported that indicates a 'break'.

Is there a way I can find out what this character is and maybe replace it with a space or something?
thanks!

 

[Rick Rothstein]

The character is a Line Feed character. Its ASCII value is 10 so you can replace it with a space character like this...

=SUBSTITUTE(cell,CHAR(10)," ")

where I use cell like you did to represent a cell reference.

 

[normpam]

Thanks. Now I can think about how I can do the next steps!

 

[Yongle]

I think Rick has already provided your solution, but here is a method to help identify every character in a string
Place the string in A1, and the 2 formulas in B1 and D1 respectively

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​	48 Limestone St. Markham, ON L6B0P6	52​	=CODE(MID($A$1,ROW(A1),1))	4	=CHAR(B1)
2​		56​		8
3​		32​
4​		76​		L
5​		105​		i
6​		109​		m
7​		101​		e
8​		115​		s
9​		116​		t
10​		111​		o
11​		110​		n
12​		101​		e
13​		32​
14​		83​		S
15​		116​		t
16​		46​		.
17​		10​
18​		77​		M
19​		97​		a
20​		114​		r
21​		107​		k
22​		104​		h
23​		97​		a
24​		109​		m
25​		44​		,
26​		32​
27​		79​		O
28​		78​		N
29​		32​
30​		76​		L
31​		54​		6
32​		66​		B
33​		48​		0
34​		80​		P
35​		54​		6

Sheet: Sheet3

 

[normpam]

Another interesting thing I am seeing....
Although the text appears normally in the cell (48 Limestone St. Markham, ON L6bop6 Can Canada) it shows up in the formula bar on three separate lines:
48 Limestone st.
Markham, ON
Can Canada

 

[Yongle]

Another interesting thing I am seeing....

Try post#4 method - it may show up another hidden character

Perhaps CHAR(13)

 

[normpam]

Thanks.... will look into this as soon as I can....

 

[normpam]

Awesome... seems to answer both issues!