Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 -- |
#6
|
|||
|
|||
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 -- |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 -- |
#9
|
|||
|
|||
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 -- |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
Interrogating cells by cell formatting | Excel Worksheet Functions | |||
How do I link a cells formatting from one worksheet to another? | Excel Worksheet Functions | |||
Excel should allow more options for formatting cells | Excel Worksheet Functions | |||
How to copy "edge" formatting to other cells. | Excel Discussion (Misc queries) |