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

[Artorius]

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.

 

[pgc01]

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]

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]

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]

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

 

[Artorius]

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]

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]

Hi again

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

Paste in a general module:

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]

This worked an absolute dream...... character code was 13 ...... splits my address line perfectly.

Thanks pgc .....