Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell cropping with a Macro or VB
I need to be able via a macro to copy only a portion of a cells contents to a
different part of the sheet. What I have is like below:- "CLOBMIDDLETON PBL" or "CLOBDIDCOT PBL" I need to get ride of the "PBL" part and as you can see the first part of the cell varies in length. Is there any way to tell excel to copy till the space? Thanks............. Alastair. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell cropping with a Macro or VB
couldn't you just use editreplacePBL""
-- Don Guillett SalesAid Software "Alastair79" wrote in message ... I need to be able via a macro to copy only a portion of a cells contents to a different part of the sheet. What I have is like below:- "CLOBMIDDLETON PBL" or "CLOBDIDCOT PBL" I need to get ride of the "PBL" part and as you can see the first part of the cell varies in length. Is there any way to tell excel to copy till the space? Thanks............. Alastair. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell cropping with a Macro or VB
iloc = Instr(1,activecell," ",vbTextcompare)
s = left(activecell,iloc-1) activecell.offset(0,5).Value = s if the part to remove is always 3 characters and a space s = left(activecell,len(activecell)-4) activecell.offset(0,5).value = s -- Regards, Tom Ogilvy "Alastair79" wrote: I need to be able via a macro to copy only a portion of a cells contents to a different part of the sheet. What I have is like below:- "CLOBMIDDLETON PBL" or "CLOBDIDCOT PBL" I need to get ride of the "PBL" part and as you can see the first part of the cell varies in length. Is there any way to tell excel to copy till the space? Thanks............. Alastair. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell cropping with a Macro or VB
If PBL is always at the end of the string and preceded by a blank:
in B1 put: =LEFT(A1,LEN(A1)-4) and copy down. You can then copy (Col B)/pastespecial/values into col A. "Alastair79" wrote: I need to be able via a macro to copy only a portion of a cells contents to a different part of the sheet. What I have is like below:- "CLOBMIDDLETON PBL" or "CLOBDIDCOT PBL" I need to get ride of the "PBL" part and as you can see the first part of the cell varies in length. Is there any way to tell excel to copy till the space? Thanks............. Alastair. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell cropping with a Macro or VB
Hello
No real need for a macro there but anyway: Either Left(Range("A1").Value, Len(Range("A1").Value) - 4) Or MID(Range("A1").Value),1,FIND("" "",Range("A1").Value,1)-1) will return the text without the " PBL" part. HTH Cordially Pascal "Alastair79" a écrit dans le message de news: ... I need to be able via a macro to copy only a portion of a cells contents to a different part of the sheet. What I have is like below:- "CLOBMIDDLETON PBL" or "CLOBDIDCOT PBL" I need to get ride of the "PBL" part and as you can see the first part of the cell varies in length. Is there any way to tell excel to copy till the space? Thanks............. Alastair. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cropping | Excel Discussion (Misc queries) | |||
Why is Print Preview cropping off data? | Excel Discussion (Misc queries) | |||
Picture Cropping Challenge | Excel Programming | |||
Intelligent picture cropping | Excel Programming | |||
Question: Cell formula or macro to write result of one cell to another cell | Excel Programming |