Help formatting cells
 

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

Help formatting cells
 

Assuming numbers in A1 down
Try in B1: =LEFT(TRIM(A1),4)+0
Copy down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Gordon Baxter" wrote in message
...
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

Help formatting cells
 

Add a helper column

=LEFT(A1,4)

and copy down

--

HTH

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


"Gordon Baxter" wrote in message
...
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

Help formatting cells
 

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


"Bob Phillips" wrote in message
...
Add a helper column

=LEFT(A1,4)

and copy down

--

HTH

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


"Gordon Baxter" wrote in message
...
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

Help formatting cells
 

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

Try instead in B1, copied down:
=LEFT(SUBSTITUTE(TRIM(A1),"£",""),4)+0
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Help formatting cells
 

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


"Max" wrote in message
...
"Gordon Baxter" wrote:
.. Is there anyway I can format the cells to
delete the £ sign which appears in some cells?

Try instead in B1, copied down:
=LEFT(SUBSTITUTE(TRIM(A1),"£",""),4)+0
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Help formatting cells
 

Think we could put in B1, and copy down:
=LEFT(A1,SEARCH("£",A1)-1)+0
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Gordon Baxter" wrote in message
...
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

Help formatting cells
 

Gordon,

Another alternative

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

--

HTH

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


"Gordon Baxter" wrote in message
...
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


"Max" wrote in message
...
"Gordon Baxter" wrote:
.. Is there anyway I can format the cells to
delete the £ sign which appears in some cells?

Try instead in B1, copied down:
=LEFT(SUBSTITUTE(TRIM(A1),"£",""),4)+0
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Help formatting cells
 

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" wrote in message
...
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


"Max" wrote in message
...
"Gordon Baxter" wrote:
.. Is there anyway I can format the cells to
delete the £ sign which appears in some cells?

Try instead in B1, copied down:
=LEFT(SUBSTITUTE(TRIM(A1),"£",""),4)+0
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Help formatting cells
 

Brilliant, that solves it.

Thanks for your help.

Gordon

"Max" wrote in message
...
Think we could put in B1, and copy down:
=LEFT(A1,SEARCH("£",A1)-1)+0
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Gordon Baxter" wrote in message
...
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

Help formatting cells
 

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Gordon Baxter" wrote in message
...
Brilliant, that solves it.

Thanks for your help.

Gordon