Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing one part of an address
I have excel 2003 and XP. I have a worksheet with some 50,000 entries.
Column E is an address field. Some 7,000 of the addresses have a suite number in the format of 123 Main St # A 456 Elm St 789 Oak Ave # 44 I need to take delete the space to the left and to the right of the # and place the value (the A and the 44 above example) into column F. Is there a formula that I can paste and fill in column E (and if so, would not return an error code in column F if the row in column E does not have a # )? My skills are very, very basic and I do not know VBA . thank you for your time |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing one part of an address
Assume those 3 addresses are in E2:E4. Put this formula in F2 and copy
down into F3 and F4: =IF(ISNUMBER(SEARCH("#",E2)),RIGHT(E2,LEN(E2)-SEARCH("#",E2)-1),"") There is no way you can put a formula in column E which will affect the value within column E, but if you wanted to remove everything to the right of a # in column E, you could put this in G2 (say) and copy down: =IF(ISNUMBER(SEARCH("#",E2)),LEFT(E2,SEARCH("#",E2 )-1),E2) Hope this helps. Pete On Nov 13, 12:54*am, LSSR wrote: I have excel 2003 and XP. *I have a worksheet with some 50,000 entries. * Column E is an address field. * Some 7,000 of the addresses have a suite number in the format of 123 Main St # A 456 Elm St 789 Oak Ave # 44 I need to take delete the space to the left and to the right of the # and place the value (the A and the 44 above example) into column F. Is there a formula that I can paste and fill in column E (and if so, would not return an error code in column F if the row in column E does not have a # )? *My skills are very, very basic and I do not know VBA . thank you for your time |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing one part of an address
You can do what you want in two steps without using any formulas or VBA.
1. Select Column E and then click Edit/Replace on the menu bar. In the "Find what" field, place " # " WITHOUT the quote marks (that is, put space/#/space characters in the field and then put just a # symbol in the "Replace with" field. Finally, click the "Replace All" button. Doing this will remove the spaces from around the # symbol. 2. With Column E still selected, click Data/Text To Columns, select the Delimited option button and click the Next button. Select the Other check box and put a # symbol in the field next to that check box, then click the Next button. Select the second column in the chart at the bottom of the dialog panel and then select the Text option button in the upper right corner of the panel (assuming you want the suite numbers, when they are numbers, to be text to match the text that the letters will be). Finally, click the Finish button. -- Rick (MVP - Excel) "LSSR" wrote in message ... I have excel 2003 and XP. I have a worksheet with some 50,000 entries. Column E is an address field. Some 7,000 of the addresses have a suite number in the format of 123 Main St # A 456 Elm St 789 Oak Ave # 44 I need to take delete the space to the left and to the right of the # and place the value (the A and the 44 above example) into column F. Is there a formula that I can paste and fill in column E (and if so, would not return an error code in column F if the row in column E does not have a # )? My skills are very, very basic and I do not know VBA . thank you for your time |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing one part of an address
That you both for your time. Both examples worked wonderfully.
"Rick Rothstein" wrote: You can do what you want in two steps without using any formulas or VBA. 1. Select Column E and then click Edit/Replace on the menu bar. In the "Find what" field, place " # " WITHOUT the quote marks (that is, put space/#/space characters in the field and then put just a # symbol in the "Replace with" field. Finally, click the "Replace All" button. Doing this will remove the spaces from around the # symbol. 2. With Column E still selected, click Data/Text To Columns, select the Delimited option button and click the Next button. Select the Other check box and put a # symbol in the field next to that check box, then click the Next button. Select the second column in the chart at the bottom of the dialog panel and then select the Text option button in the upper right corner of the panel (assuming you want the suite numbers, when they are numbers, to be text to match the text that the letters will be). Finally, click the Finish button. -- Rick (MVP - Excel) "LSSR" wrote in message ... I have excel 2003 and XP. I have a worksheet with some 50,000 entries. Column E is an address field. Some 7,000 of the addresses have a suite number in the format of 123 Main St # A 456 Elm St 789 Oak Ave # 44 I need to take delete the space to the left and to the right of the # and place the value (the A and the 44 above example) into column F. Is there a formula that I can paste and fill in column E (and if so, would not return an error code in column F if the row in column E does not have a # )? My skills are very, very basic and I do not know VBA . thank you for your time |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parsing one part of an address
You're welcome.
Pete On Nov 13, 12:37*pm, LSSR wrote: That you both for your time. *Both examples worked wonderfully. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parsing part of an address | Excel Discussion (Misc queries) | |||
Another Parsing address question | Excel Discussion (Misc queries) | |||
parsing out an undelimited address and city | Excel Discussion (Misc queries) | |||
parsing out an undelimited address and city | Excel Discussion (Misc queries) | |||
Parsing address info | Excel Discussion (Misc queries) |