advanced formula question ... ?

[philipgalitzine]

Hey folks, here's my query if anyone can help:

I've got a spreadsheet with one column in it containing organization
names and addresses. Some cells start with the address - specifically
the street number - and some rows start with the organization name.

What I would like to do is build a formula that says if the first
character in the cell is a number leave it alone, but if the first
character in the cell is a letter, cut the content of the cell
starting at the left margin and ending at the comma, and paste it into
a second column.

In other words, take the organization name and put it in a new column,
and if there is no organization name, just the street number, do
nothing.

Is this possible, and if so, how would I do it?

Thanks in advance!

Phil

 

[Bob Greenblatt]

Hey folks, here's my query if anyone can help:

I've got a spreadsheet with one column in it containing organization
names and addresses. Some cells start with the address - specifically
the street number - and some rows start with the organization name.

What I would like to do is build a formula that says if the first
character in the cell is a number leave it alone, but if the first
character in the cell is a letter, cut the content of the cell
starting at the left margin and ending at the comma, and paste it into
a second column.

In other words, take the organization name and put it in a new column,
and if there is no organization name, just the street number, do
nothing.

Is this possible, and if so, how would I do it?

Thanks in advance!

Phil

This is possible with VBA code, and to a lesser extent with worksheet
formulas. You mentioned that you want to "break" the string at the first
comma? Is this really correct? What about organization, Inc.? Also are there
multiple lines in the cell? If so, is the line breaking via Excel's wrap
text, or do you have a new line character? If the former, it is not
possible to determine where the line break is.

This formula will give you the organization name =LEFT(A1,SEARCH(",",A1)-1)

This will give the right (address part of the cell:
=RIGHT(A1,LEN(A1)-SEARCH(",",A1))

Put these formulas in a convenient column(s) and fill down. The copy the
column and paste special-values to remove the formulas. Then you can delete
the original columns and rearrange them as you need.

 

[philipgalitzine]

This is possible with VBA code, and to a lesser extent with worksheet
formulas. You mentioned that you want to "break" the string at the first
comma? Is this really correct? What about organization, Inc.? Also are there
multiple lines in the cell? If so, is the line breaking via Excel's wrap
text, or do you have a new line character? If the former, it is not
possible to determine where the line break is.

This formula will give you the organization name =LEFT(A1,SEARCH(",",A1)-1)

This will give the right (address part of the cell:
=RIGHT(A1,LEN(A1)-SEARCH(",",A1))

Put these formulas in a convenient column(s) and fill down. The copy the
column and paste special-values to remove the formulas. Then you can delete
the original columns and rearrange them as you need.

Bob, thank you! Just the help I needed.