Remove words in a cell

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:52 PM GMT

Question

Contents of cell A1 = Po Box 678876 Springfield Contents of A2 = 156 Main Road Neverland Cell contents not all the same length and no real common data in each cell. I want to be able to split the cell as follows: A1=Po Box 678876 B1=Springfield A2=156 Main Road B2=Neverland

Answer

The only way I can think of to do this is with a custom function ¨C

Press Alt F11 to get into the VBA editor, insert a module and paste

Public Function getlastword(myvar As String) While InStr(myvar, " ") > 0 myvar = Trim(Right(myvar, Len(myvar) - InStr(myvar, " "))) Wend getlastword = myvar End Function

Then close the VBA editor and in column C enter =getlastword(A1) in column B enter =TRIM(LEFT(A1,LEN(TRIM(A1))-LEN(C1)))


Once you have the results you can copy and paste special, values over the original cells.

Question

Hello Contents of cell A1 = Po Box 678876 Springfield Contents of A2 = 156 Main Road Neverland Cell contents not all the same length and no real common data in each cell. I want to be able to split the cell as follows: A1=Po Box 678876 B1=Springfield A2=156 Main Road B2-Neverland

Answer

Nola

Sorry I do not know of an easy way to separate the text into what you need.

What I usually do in this situation is try to find the most common areas in the text strings where a break occurs, then use the text to columns feature (toolbar/data/text to columns) to separate the text into columns and then put the data that I want together back together using the & or concatenate formula. +A1&" "&B1 This formula will take the contents of cell a1 and cell b1 and put them together with a space in between.

The text strings that don't work very well with this method I then segregate from the others and work with them on a specific cell by cell basis.

I know this may not help you much and ther may be better ways, so I suggest you reask the question of other allexperts persons and maybe you can get a better answer.

Advertisement

©2024 eLuminary LLC. All rights reserved.