Imported wrap text

normpam

Board Regular
Joined
Oct 30, 2002
Messages
241
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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,926
Office Version
2010
Platform
Windows
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.
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,049
Office Version
365
Platform
Windows
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

Board Regular
Joined
Oct 30, 2002
Messages
241
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,049
Office Version
365
Platform
Windows
Another interesting thing I am seeing....
Try post#4 method - it may show up another hidden character

Perhaps CHAR(13)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,505
Messages
5,469,017
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top