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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]48 Limestone St.
Markham, ON L6B0P6[/td][td]

52​

[/td][td] =CODE(MID($A$1,ROW(A1),1))[/td][td]4[/td][td] =CHAR(B1)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td]

56​

[/td][td][/td][td]8[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td]

32​

[/td][td][/td][td] [/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td]

76​

[/td][td][/td][td]L[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td]

105​

[/td][td][/td][td]i[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td]

109​

[/td][td][/td][td]m[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td]

101​

[/td][td][/td][td]e[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td]

115​

[/td][td][/td][td]s[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td]

116​

[/td][td][/td][td]t[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td]

111​

[/td][td][/td][td]o[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td]

110​

[/td][td][/td][td]n[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td]

101​

[/td][td][/td][td]e[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td]

32​

[/td][td][/td][td] [/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td]

83​

[/td][td][/td][td]S[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][td]

116​

[/td][td][/td][td]t[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td][/td][td]

46​

[/td][td][/td][td].[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td][/td][td=bgcolor:#FFFF00]

10​

[/td][td][/td][td]
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td][/td][td]

77​

[/td][td][/td][td]M[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td][/td][td]

97​

[/td][td][/td][td]a[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td][/td][td]

114​

[/td][td][/td][td]r[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td][/td][td]

107​

[/td][td][/td][td]k[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td][/td][td]

104​

[/td][td][/td][td]h[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td][/td][td]

97​

[/td][td][/td][td]a[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td][/td][td]

109​

[/td][td][/td][td]m[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25​

[/td][td][/td][td]

44​

[/td][td][/td][td],[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

26​

[/td][td][/td][td]

32​

[/td][td][/td][td] [/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

27​

[/td][td][/td][td]

79​

[/td][td][/td][td]O[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

28​

[/td][td][/td][td]

78​

[/td][td][/td][td]N[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

29​

[/td][td][/td][td]

32​

[/td][td][/td][td] [/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

30​

[/td][td][/td][td]

76​

[/td][td][/td][td]L[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

31​

[/td][td][/td][td]

54​

[/td][td][/td][td]6[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

32​

[/td][td][/td][td]

66​

[/td][td][/td][td]B[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

33​

[/td][td][/td][td]

48​

[/td][td][/td][td]0[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

34​

[/td][td][/td][td]

80​

[/td][td][/td][td]P[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

35​

[/td][td][/td][td]

54​

[/td][td][/td][td]6[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

 

[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!