Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi everyone,
I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi,
Can you give some example of the names? Are they surnames only? Forenames & Surnames? A middle initial/name? or combinations of the above? Mike "CAT" wrote: Hi everyone, I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi,
On reflection if the cell eith contains just a name or a name and the word address then this should work =IF(ISERROR(SEARCH("Address",A1)),A1,TRIM(LEFT(A1, SEARCH("Address",A1)-1))) Mike "Mike H" wrote: Hi, Can you give some example of the names? Are they surnames only? Forenames & Surnames? A middle initial/name? or combinations of the above? Mike "CAT" wrote: Hi everyone, I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi Mike,
Just surnames, no commas and the words "address" or "telephone number" or "address and tel nr"; Mike, do I run your formula first for removing the word "address", then run it again changing "telephone number" for "address" in the formula, and so on for the 3rd deletion? Thank you "Mike H" wrote: Hi, On reflection if the cell eith contains just a name or a name and the word address then this should work =IF(ISERROR(SEARCH("Address",A1)),A1,TRIM(LEFT(A1, SEARCH("Address",A1)-1))) Mike "Mike H" wrote: Hi, Can you give some example of the names? Are they surnames only? Forenames & Surnames? A middle initial/name? or combinations of the above? Mike "CAT" wrote: Hi everyone, I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi,
If it's just a surname followed by a space then this works. With your data in column A starting in a1 put this in b1 and drag down =IF(ISERROR(SEARCH(" ",A1)),A1,TRIM(LEFT(A1,SEARCH(" ",A1)-1))) Mike "CAT" wrote: Hi Mike, Just surnames, no commas and the words "address" or "telephone number" or "address and tel nr"; Mike, do I run your formula first for removing the word "address", then run it again changing "telephone number" for "address" in the formula, and so on for the 3rd deletion? Thank you "Mike H" wrote: Hi, On reflection if the cell eith contains just a name or a name and the word address then this should work =IF(ISERROR(SEARCH("Address",A1)),A1,TRIM(LEFT(A1, SEARCH("Address",A1)-1))) Mike "Mike H" wrote: Hi, Can you give some example of the names? Are they surnames only? Forenames & Surnames? A middle initial/name? or combinations of the above? Mike "CAT" wrote: Hi everyone, I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi Mike,
Apologies, my info was not quite correct: First of all the column with the data is col D; secondly, there are more than one name before the text to be removed, sometimes two and up to five names (just checked) with no punctuation but just spaces. I have entered your formula in cell d1 and copied and pasted it down col D and it erased everything, returning me 0 in the right hand corner of each cell! As you can ascertain by this, I am a newbie; I use Excell 2007 by the way. Thank you for your help CAT "Mike H" wrote: Hi, If it's just a surname followed by a space then this works. With your data in column A starting in a1 put this in b1 and drag down =IF(ISERROR(SEARCH(" ",A1)),A1,TRIM(LEFT(A1,SEARCH(" ",A1)-1))) Mike "CAT" wrote: Hi Mike, Just surnames, no commas and the words "address" or "telephone number" or "address and tel nr"; Mike, do I run your formula first for removing the word "address", then run it again changing "telephone number" for "address" in the formula, and so on for the 3rd deletion? Thank you "Mike H" wrote: Hi, On reflection if the cell eith contains just a name or a name and the word address then this should work =IF(ISERROR(SEARCH("Address",A1)),A1,TRIM(LEFT(A1, SEARCH("Address",A1)-1))) Mike "Mike H" wrote: Hi, Can you give some example of the names? Are they surnames only? Forenames & Surnames? A middle initial/name? or combinations of the above? Mike "CAT" wrote: Hi everyone, I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi,
Post some sample data and the result you expect to get from that data Mike "CAT" wrote: Hi Mike, Apologies, my info was not quite correct: First of all the column with the data is col D; secondly, there are more than one name before the text to be removed, sometimes two and up to five names (just checked) with no punctuation but just spaces. I have entered your formula in cell d1 and copied and pasted it down col D and it erased everything, returning me 0 in the right hand corner of each cell! As you can ascertain by this, I am a newbie; I use Excell 2007 by the way. Thank you for your help CAT "Mike H" wrote: Hi, If it's just a surname followed by a space then this works. With your data in column A starting in a1 put this in b1 and drag down =IF(ISERROR(SEARCH(" ",A1)),A1,TRIM(LEFT(A1,SEARCH(" ",A1)-1))) Mike "CAT" wrote: Hi Mike, Just surnames, no commas and the words "address" or "telephone number" or "address and tel nr"; Mike, do I run your formula first for removing the word "address", then run it again changing "telephone number" for "address" in the formula, and so on for the 3rd deletion? Thank you "Mike H" wrote: Hi, On reflection if the cell eith contains just a name or a name and the word address then this should work =IF(ISERROR(SEARCH("Address",A1)),A1,TRIM(LEFT(A1, SEARCH("Address",A1)-1))) Mike "Mike H" wrote: Hi, Can you give some example of the names? Are they surnames only? Forenames & Surnames? A middle initial/name? or combinations of the above? Mike "CAT" wrote: Hi everyone, I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
On Wed, 10 Dec 2008 09:21:01 -0800, CAT wrote:
Hi everyone, I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT Here is a VBA solution with a User Defined Function. <alt-F11 opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. If your data starts in, let us say, B2, enter the following formula into some cell: =RegexSub(B2,"[\s\W]+(Address|Telephone|Tel Nr)[\s\S]+","") and fill down as far as required. This can be easily modified in case you have missed something in your description. ================================ Option Explicit Function RegexSub(Str As String, SrchFor As String, ReplWith As String) _ As String Dim objRegExp As Object Set objRegExp = CreateObject("vbscript.regexp") objRegExp.Pattern = SrchFor objRegExp.IgnoreCase = True objRegExp.Global = True objRegExp.MultiLine = True RegexSub = objRegExp.Replace(Str, ReplWith) End Function ============================ --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi Mike,
For ex: In Cell B2: Kirriemuir Barratt Moss (IRE) Address Telephone Number I want to end up with: Kirriemuir Barratt Moss (IRE) and eliminate Address and Telephone Number from Cell B2 In Cell B3, I might have: Bridge Oldrik (GER) Telephone Number I want to end up with: Bridge Oldrik (GER) and eliminate Telephone Number from cell B3 and so on; hope it makes sense. CAT "Mike H" wrote: Hi, Post some sample data and the result you expect to get from that data Mike "CAT" wrote: Hi Mike, Apologies, my info was not quite correct: First of all the column with the data is col D; secondly, there are more than one name before the text to be removed, sometimes two and up to five names (just checked) with no punctuation but just spaces. I have entered your formula in cell d1 and copied and pasted it down col D and it erased everything, returning me 0 in the right hand corner of each cell! As you can ascertain by this, I am a newbie; I use Excell 2007 by the way. Thank you for your help CAT "Mike H" wrote: Hi, If it's just a surname followed by a space then this works. With your data in column A starting in a1 put this in b1 and drag down =IF(ISERROR(SEARCH(" ",A1)),A1,TRIM(LEFT(A1,SEARCH(" ",A1)-1))) Mike "CAT" wrote: Hi Mike, Just surnames, no commas and the words "address" or "telephone number" or "address and tel nr"; Mike, do I run your formula first for removing the word "address", then run it again changing "telephone number" for "address" in the formula, and so on for the 3rd deletion? Thank you "Mike H" wrote: Hi, On reflection if the cell eith contains just a name or a name and the word address then this should work =IF(ISERROR(SEARCH("Address",A1)),A1,TRIM(LEFT(A1, SEARCH("Address",A1)-1))) Mike "Mike H" wrote: Hi, Can you give some example of the names? Are they surnames only? Forenames & Surnames? A middle initial/name? or combinations of the above? Mike "CAT" wrote: Hi everyone, I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi Ron,
Thank you. I have never used VBA (although I have a book about it); I will try and follow your instructions in a new book: copy and paste my downloaded lists in it and see what happens. Just in case I do it all wrong, how can I get rid of a VBA Code and start again? I know it's not as simple as clearing a formula from a worksheet. Thank you again CAT "Ron Rosenfeld" wrote: On Wed, 10 Dec 2008 09:21:01 -0800, CAT wrote: Hi everyone, I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT Here is a VBA solution with a User Defined Function. <alt-F11 opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. If your data starts in, let us say, B2, enter the following formula into some cell: =RegexSub(B2,"[\s\W]+(Address|Telephone|Tel Nr)[\s\S]+","") and fill down as far as required. This can be easily modified in case you have missed something in your description. ================================ Option Explicit Function RegexSub(Str As String, SrchFor As String, ReplWith As String) _ As String Dim objRegExp As Object Set objRegExp = CreateObject("vbscript.regexp") objRegExp.Pattern = SrchFor objRegExp.IgnoreCase = True objRegExp.Global = True objRegExp.MultiLine = True RegexSub = objRegExp.Replace(Str, ReplWith) End Function ============================ --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
On Wed, 10 Dec 2008 14:40:04 -0800, CAT wrote:
Hi Ron, Thank you. I have never used VBA (although I have a book about it); I will try and follow your instructions in a new book: copy and paste my downloaded lists in it and see what happens. Just in case I do it all wrong, how can I get rid of a VBA Code and start again? I know it's not as simple as clearing a formula from a worksheet. Thank you again CAT You can just delete everything in the module that you created (in the VB Editor). Or you can right click on the module name in the Project Explorer window (it'll probably be Module1), and select "Remove module" from the drop-down list. When it asks if you want to save it first, select No. --ron |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
On Wed, 10 Dec 2008 14:40:04 -0800, CAT wrote:
Hi Ron, Thank you. I have never used VBA (although I have a book about it); I will try and follow your instructions in a new book: copy and paste my downloaded lists in it and see what happens. Just in case I do it all wrong, how can I get rid of a VBA Code and start again? I know it's not as simple as clearing a formula from a worksheet. Thank you again CAT CAT, Looking at the examples you posted to Mike H, you will need to change the function I posted to: =RegexSub(D1,"\s+(Address|Telephone|Tel Nr)[\s\S]+","") --ron |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi Ron,
I will try and use your code tomorrow. I suppose I better close this query now (wether or not I can make it work!) Again thank you for your help CAT "Ron Rosenfeld" wrote: On Wed, 10 Dec 2008 14:40:04 -0800, CAT wrote: Hi Ron, Thank you. I have never used VBA (although I have a book about it); I will try and follow your instructions in a new book: copy and paste my downloaded lists in it and see what happens. Just in case I do it all wrong, how can I get rid of a VBA Code and start again? I know it's not as simple as clearing a formula from a worksheet. Thank you again CAT CAT, Looking at the examples you posted to Mike H, you will need to change the function I posted to: =RegexSub(D1,"\s+(Address|Telephone|Tel Nr)[\s\S]+","") --ron |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
On Wed, 10 Dec 2008 17:36:07 -0800, CAT wrote:
Hi Ron, I will try and use your code tomorrow. I suppose I better close this query now (wether or not I can make it work!) Again thank you for your help CAT Please post back in this thread and let us know how you're doing. No need to "close the query". --ron |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing part(s) of text from downloaded data
Hi,
Is there always a ")" in the string. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "CAT" wrote in message ... Hi Mike, For ex: In Cell B2: Kirriemuir Barratt Moss (IRE) Address Telephone Number I want to end up with: Kirriemuir Barratt Moss (IRE) and eliminate Address and Telephone Number from Cell B2 In Cell B3, I might have: Bridge Oldrik (GER) Telephone Number I want to end up with: Bridge Oldrik (GER) and eliminate Telephone Number from cell B3 and so on; hope it makes sense. CAT "Mike H" wrote: Hi, Post some sample data and the result you expect to get from that data Mike "CAT" wrote: Hi Mike, Apologies, my info was not quite correct: First of all the column with the data is col D; secondly, there are more than one name before the text to be removed, sometimes two and up to five names (just checked) with no punctuation but just spaces. I have entered your formula in cell d1 and copied and pasted it down col D and it erased everything, returning me 0 in the right hand corner of each cell! As you can ascertain by this, I am a newbie; I use Excell 2007 by the way. Thank you for your help CAT "Mike H" wrote: Hi, If it's just a surname followed by a space then this works. With your data in column A starting in a1 put this in b1 and drag down =IF(ISERROR(SEARCH(" ",A1)),A1,TRIM(LEFT(A1,SEARCH(" ",A1)-1))) Mike "CAT" wrote: Hi Mike, Just surnames, no commas and the words "address" or "telephone number" or "address and tel nr"; Mike, do I run your formula first for removing the word "address", then run it again changing "telephone number" for "address" in the formula, and so on for the 3rd deletion? Thank you "Mike H" wrote: Hi, On reflection if the cell eith contains just a name or a name and the word address then this should work =IF(ISERROR(SEARCH("Address",A1)),A1,TRIM(LEFT(A1, SEARCH("Address",A1)-1))) Mike "Mike H" wrote: Hi, Can you give some example of the names? Are they surnames only? Forenames & Surnames? A middle initial/name? or combinations of the above? Mike "CAT" wrote: Hi everyone, I would be grateful for a little help here with a formula to remove parts of text in a column containing downloaded data. In colomn B, with around 300 cells, I have a name, then the words "address" and/or "telephone number" or "address and tel nr" (no punctuations marks); those words do NOTappear in every cell: in some cases only the name is downloaded. I would like to use a formula to "clean up" the data in this column: to find and delete those words and be left with only the name (which is of course different in each cell). I have used conditional formatting to highlight those bits of text and deleted them manually but it's taking me all day, there must be an easier way. Thank you in advance for your help CAT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text in a file downloaded to excel | Excel Discussion (Misc queries) | |||
removing a letter to the beginning of each part number of a column | Excel Discussion (Misc queries) | |||
Removing part of a number in Excel | Excel Discussion (Misc queries) | |||
removing part of text string to another cell | Excel Discussion (Misc queries) | |||
removing part of a # | Excel Worksheet Functions |