Help formatting cells

G

Gordon Baxter

I have a column of numbers which I have imported from a website into a
spreadsheet.

Each cell has up to 8 characters. I want to discard everything but say the
first 4 chararcters.

Can this be done?

Gordon
 
B

Bob Phillips

Add a helper column

=LEFT(A1,4)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
G

Gordon Baxter

Excellent, both appear to work. Thanks.

This has given me another slight problem in that I am sometimes left with a
two digit number and a £ sign. Is there anyway I can format the cells to
delete the £ sign which appears in some cells?

Gordon
 
M

Max

Gordon Baxter said:
.. Is there anyway I can format the cells to
delete the £ sign which appears in some cells?
Click to expand...

Try instead in B1, copied down:
=LEFT(SUBSTITUTE(TRIM(A1),"£",""),4)+0
 
G

Gordon Baxter

No that doesn't work Max. Perhaps I have not explained it very well.

The following are samples of figures I get: 410 £2, 60 £19, 700 £4, 29 £36.

After reducing these down to the first four digits I am left with: 410, 60
£, 700, 29 £.

I am looking to get rid of the £ sign following the 60 & 29.

Regards
Gordon
 
B

Bob Phillips

Gordon,

Another alternative

=LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(B1),"£","")," ",""),4)+0

--

HTH

RP
(remove nothere from the email address if mailing direct)


Gordon Baxter said:
No that doesn't work Max. Perhaps I have not explained it very well.

The following are samples of figures I get: 410 £2, 60 £19, 700 £4, 29 £36.

After reducing these down to the first four digits I am left with: 410, 60
£, 700, 29 £.

I am looking to get rid of the £ sign following the 60 & 29.

Regards
Gordon
Click to expand...
 
B

Bob Phillips

or even

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1),"£",""),CHAR(32),""),CHAR(16
0),""),4)+0

if it still isn't quite working

--

HTH

RP
(remove nothere from the email address if mailing direct)


Gordon Baxter said:
No that doesn't work Max. Perhaps I have not explained it very well.

The following are samples of figures I get: 410 £2, 60 £19, 700 £4, 29 £36.

After reducing these down to the first four digits I am left with: 410, 60
£, 700, 29 £.

I am looking to get rid of the £ sign following the 60 & 29.

Regards
Gordon
Click to expand...
 
Top