Concatenate Text & Numbers Keeping zeros..

[glynny]

Hello all, I have a very similar problem to a previous post which is
driving me mad...

5 column spreadsheet :

A1 : A2 :A3 :A4 : A5

Empty : 111 Somewhere Road B23 4DF : 1.14565 : 52.2321 : 005
Empty : 112 Somewhere Avenue B26 5DF: 1.14541 : 52.5654: 010

I need to combine A2 & A5 into the empty position at A1

The A5 column is formatted to show leading zeros, and is the result of
a very complicated equation. (Haversine calc for Great Circle distance
using Lat /Longs)

The A1 column must be of the form :

005 Miles 111 Somewhere Road B23 4DF
010 Miles 112 Somewhere Avenue B26 5DF

I have tried everything to get it in this format, but I usually end up
either without the zeros and correct text or correct zeros & no text

The A5 column is formatted using custom 000.0
I have tried Concatenate (A5,A2) with a text format using displayed
value not 15 digit value

and using &TEXT but I can't seem to get exactly what I want.

any help greatly appreciated........

Glynny

 

[Pete_UK]

So, have you tried:

=TEXT(E1,"000.0")&" Miles "&B1
or even
=TEXT(A5,"000.0")&" Miles "&A2

Your references are very confusing !!

Hope this helps.

Pete

 

[glynny]

Pete_UK you are a star.

Why is it that this is totally obvious this morning & a total enigma
last night.

Sorry about the references - was having a meltdown last night.

yhanks again

Glynny

 

[Pete_UK]

Glad you got it working, thanks for feeding back.

Pete