Text to Columns

[rodgo]

Help! I imported a list of addresses from Google Maps. The column containing street address also contains distance separated by a vertical line, ex. (526 Main Road | 33m), the last portion of which I need to eliminate. The list is very long. The text to columns function does not seem to be a solution. Would a macro work for this?

[rodgo]

Resolved. Discovered the vertical line key and used it in text to columns.

[AlKey]

Or you can use formula

=TRIM(LEFT(SUBSTITUTE(A1," |",REPT(" ",255)),255))

[sktneer]

Why don't you use a formula to achieve this....

Assuming that you have address in cell A1, then use the following formula to return the desired portion of the address.

Please Login or Register  to view this content.

Is this what you want?

[rodgo]

Thanks, but for some reason kept getting circular error.

[rodgo]

Thanks, but for some reason could not get this to work.

[sktneer]

Which formula did you use? There is no point of circular reference if you place the formula in B1. If you place the formula in A1 itself (which you are not supposed to do), you will get the circular reference warning.

After importing the data in sheet in col. A, if you want the changed address in the col. A itself, you will need a macro in that case.

[rodgo]

Aha! The key here is placing the formula in B1, not A1 as I did. Your formula works just fine, as does the one from AlKey. Thank you both.

[sktneer]

Glad to help you. Thanks for the feedback and the Rep.