Address cell includes 2 lines of address split by ASCI box?

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
I have a cell for "Address1" which is in say Column A starting at A2.

"Address1" contains the first 2 lines of an address, which is separated in the excel field by a square box. I seem to recall this is an ASCI character that forces a CARRIAGE RETURN when printing out or something?

Question:

I need to split "Address1" into 2 separate fields, say in Column B & C. Does anyone know how I can achieve this please? Thanks.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi Artorius

If instead of the box you want to see the 2 lines: Format Cells>Alignment>Wrap text.

To split the text:

In B2:
=LEFT(A2,-1+FIND(CHAR(10),A2))


In C2:
=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))

If you have a column with many cells such as this one you may insted use Data>Text to columns with delimeter ALT 010

Hope this helps
PGC
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
Not sure why but both Formulas give me a #VALUE! error message.

I also tried the Text to Columns but I think I may have done something wrong here. For the delimiter ... I clicked in Other and then tried a combination of pressing ALT 010 (zero one zero) ... also ALT F10 in case it was supposed to be this :) I just got the first line of the address and the second line after the box had disappeared.

Any additional thoughts as to where I'm going wrong? Thanks .....
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi again

I assumed the delimeter was the Char(10) since this is the usual one.

Since you have a different one you must first get its code.

Check the position int the text where the delimeter is and use

=CODE(MID(A2,pos,1))

Then use the value you get in the formulas I gave you.

If the code was not right, the Text to Columns could not work. Your first choice was right, It was ALT 010.

Hope it helps
PGC
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176

ADVERTISEMENT

I do have the CODE function in Excel .... is it an add in or just available with the developers edition?
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
I do NOT have the Code function and as I'm at work there is a limitation on downloading any software etc. Is there another way open to me?

Thanks pgc01 for your help with this, much appreciated.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871

ADVERTISEMENT

You say "I do have the CODE function" but from the rest of the text I believe that you mean "I do NOT have the CODE function". If that is the case, it's strange.

CODE is one of the default functions.

From the help

/==================

CODE
See Also

Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.

Operating environment ---- Character set
Macintosh ---- Macintosh character set
Windows ----ANSI


Syntax

CODE(text)

Text is the text for which you want the code of the first character.

Examples

CODE("A") equals 65

CODE("Alphabet") equals 65

/==================

It's strange that you don't have it. Didn't you by any chance mistype it in the formula?

I will try to think of something else, in the meantime I hope you find the CODE or get other suggestions.

Kind regards
PGC
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi again

This is a workaround. Since you don't have the CODE function, implement it as an UDF.

Paste in a general module:

Code:
Function myCode(sChar As String) As Integer
myCode = Asc(sChar)
End Function

This can substitute the CODE function.

Now in a cell you can write:

=MyCode(MID(A2,pos,1))

and with the code get back to the first functions to extract the lines.

Will it work now?
PGC
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
This worked an absolute dream...... character code was 13 ...... splits my address line perfectly.

Thanks pgc .....
 

Watch MrExcel Video

Forum statistics

Threads
1,114,673
Messages
5,549,351
Members
410,910
Latest member
DessertDiva
Top