Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
left trim
Hello, I need a query that will left trim everything after the first comma in
a string. Example 1 Airport Boulevard, Gentry, AR 72734 I need just the street address to remain. Also some of the cells in the column are blank, some cells have only the city and state. Also need to know hoe the run it and from where. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
left trim
=LEFT(A1,FIND(",",A1)-1)
"Finger Tips" wrote: Hello, I need a query that will left trim everything after the first comma in a string. Example 1 Airport Boulevard, Gentry, AR 72734 I need just the street address to remain. Also some of the cells in the column are blank, some cells have only the city and state. Also need to know hoe the run it and from where. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
left trim
Where do I put this and use this?
"Teethless mama" wrote: =LEFT(A1,FIND(",",A1)-1) "Finger Tips" wrote: Hello, I need a query that will left trim everything after the first comma in a string. Example 1 Airport Boulevard, Gentry, AR 72734 I need just the street address to remain. Also some of the cells in the column are blank, some cells have only the city and state. Also need to know hoe the run it and from where. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
left trim
Copy and paste the formula ro any cell except A1.
Will return 1 Airport Boulevard into that cell as long as A1 contains 1 Airport Boulevard, Gentry, AR 72734 Gord Dibben MS Excel MVP On Sat, 28 Apr 2007 05:20:00 -0700, Finger Tips wrote: Where do I put this and use this? "Teethless mama" wrote: =LEFT(A1,FIND(",",A1)-1) "Finger Tips" wrote: Hello, I need a query that will left trim everything after the first comma in a string. Example 1 Airport Boulevard, Gentry, AR 72734 I need just the street address to remain. Also some of the cells in the column are blank, some cells have only the city and state. Also need to know hoe the run it and from where. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
left trim
Worked great thanks. How do I run it for a group of cells. I have a column
that contains several thousand cells that I need to do this for. "Gord Dibben" wrote: Copy and paste the formula ro any cell except A1. Will return 1 Airport Boulevard into that cell as long as A1 contains 1 Airport Boulevard, Gentry, AR 72734 Gord Dibben MS Excel MVP On Sat, 28 Apr 2007 05:20:00 -0700, Finger Tips wrote: Where do I put this and use this? "Teethless mama" wrote: =LEFT(A1,FIND(",",A1)-1) "Finger Tips" wrote: Hello, I need a query that will left trim everything after the first comma in a string. Example 1 Airport Boulevard, Gentry, AR 72734 I need just the street address to remain. Also some of the cells in the column are blank, some cells have only the city and state. Also need to know hoe the run it and from where. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
left trim
Place the formula in the top cell of a column adjacent to the data column then
double-click on the fill handle of the cell. i.e. data strings in column A In B1 enter the formula then hover the mouse pointer over the bottom right corner of B1. When you see a black cross, double-click on that. The formula will be copied down. When you get a few minutes(or hours) take a look at these sites for help on working with Excel. Some tutorial sites for basics of Excel........ http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm http://office.microsoft.com/en-us/tr...831141033.aspx Gord On Sat, 28 Apr 2007 13:06:01 -0700, Finger Tips wrote: Worked great thanks. How do I run it for a group of cells. I have a column that contains several thousand cells that I need to do this for. "Gord Dibben" wrote: Copy and paste the formula ro any cell except A1. Will return 1 Airport Boulevard into that cell as long as A1 contains 1 Airport Boulevard, Gentry, AR 72734 Gord Dibben MS Excel MVP On Sat, 28 Apr 2007 05:20:00 -0700, Finger Tips wrote: Where do I put this and use this? "Teethless mama" wrote: =LEFT(A1,FIND(",",A1)-1) "Finger Tips" wrote: Hello, I need a query that will left trim everything after the first comma in a string. Example 1 Airport Boulevard, Gentry, AR 72734 I need just the street address to remain. Also some of the cells in the column are blank, some cells have only the city and state. Also need to know hoe the run it and from where. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
left trim
worked perfect. Thank you very very much.
"Gord Dibben" wrote: Place the formula in the top cell of a column adjacent to the data column then double-click on the fill handle of the cell. i.e. data strings in column A In B1 enter the formula then hover the mouse pointer over the bottom right corner of B1. When you see a black cross, double-click on that. The formula will be copied down. When you get a few minutes(or hours) take a look at these sites for help on working with Excel. Some tutorial sites for basics of Excel........ http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm http://office.microsoft.com/en-us/tr...831141033.aspx Gord On Sat, 28 Apr 2007 13:06:01 -0700, Finger Tips wrote: Worked great thanks. How do I run it for a group of cells. I have a column that contains several thousand cells that I need to do this for. "Gord Dibben" wrote: Copy and paste the formula ro any cell except A1. Will return 1 Airport Boulevard into that cell as long as A1 contains 1 Airport Boulevard, Gentry, AR 72734 Gord Dibben MS Excel MVP On Sat, 28 Apr 2007 05:20:00 -0700, Finger Tips wrote: Where do I put this and use this? "Teethless mama" wrote: =LEFT(A1,FIND(",",A1)-1) "Finger Tips" wrote: Hello, I need a query that will left trim everything after the first comma in a string. Example 1 Airport Boulevard, Gentry, AR 72734 I need just the street address to remain. Also some of the cells in the column are blank, some cells have only the city and state. Also need to know hoe the run it and from where. Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
left trim
How can I now cut and paste this info in this new format to somewhere else
for use "Finger Tips" wrote: worked perfect. Thank you very very much. "Gord Dibben" wrote: Place the formula in the top cell of a column adjacent to the data column then double-click on the fill handle of the cell. i.e. data strings in column A In B1 enter the formula then hover the mouse pointer over the bottom right corner of B1. When you see a black cross, double-click on that. The formula will be copied down. When you get a few minutes(or hours) take a look at these sites for help on working with Excel. Some tutorial sites for basics of Excel........ http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm http://office.microsoft.com/en-us/tr...831141033.aspx Gord On Sat, 28 Apr 2007 13:06:01 -0700, Finger Tips wrote: Worked great thanks. How do I run it for a group of cells. I have a column that contains several thousand cells that I need to do this for. "Gord Dibben" wrote: Copy and paste the formula ro any cell except A1. Will return 1 Airport Boulevard into that cell as long as A1 contains 1 Airport Boulevard, Gentry, AR 72734 Gord Dibben MS Excel MVP On Sat, 28 Apr 2007 05:20:00 -0700, Finger Tips wrote: Where do I put this and use this? "Teethless mama" wrote: =LEFT(A1,FIND(",",A1)-1) "Finger Tips" wrote: Hello, I need a query that will left trim everything after the first comma in a string. Example 1 Airport Boulevard, Gentry, AR 72734 I need just the street address to remain. Also some of the cells in the column are blank, some cells have only the city and state. Also need to know hoe the run it and from where. Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
left trim
Finger Tips wrote...
How can I now cut and paste this info in this new format to somewhere else for use .... Well, you've already mentioned the first step - COPY. Presumably you know how to do that or could find out using online help. As for the paste step, use Paste Special rather than Paste, but you should read the help topic on Paste Special in online help for details. You'll save lots of time using online help rather than newsgroups for things like this. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
left trim
Thanks Harlan
"Harlan Grove" wrote: Finger Tips wrote... How can I now cut and paste this info in this new format to somewhere else for use .... Well, you've already mentioned the first step - COPY. Presumably you know how to do that or could find out using online help. As for the paste step, use Paste Special rather than Paste, but you should read the help topic on Paste Special in online help for details. You'll save lots of time using online help rather than newsgroups for things like this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column labels run right to left, not left to right | Excel Discussion (Misc queries) | |||
Trim | Excel Worksheet Functions | |||
Trim | Excel Discussion (Misc queries) | |||
How to change the right-to-left worksheet to left-to-right workshe | Excel Discussion (Misc queries) | |||
trim | Excel Worksheet Functions |