Removing characters from a cell

tonydoneese · #1 01-10-2016, 03:16 PM

I'm trying to modify a worksheet. I used to know the formula for this but I have forgotten. I need to remove characters from a cell and place the removed characters in another cell. The example below shows that I have 25 ea in the same cell but I need 25 in its own cell and I need ea in its own cell.

Column A
25 ea

Column A Column B
25 ea

xor · #2 01-11-2016, 02:35 AM

Extract 25: =LOOKUP(9^9,--LEFT(A1,ROW($1:$100)))

Extract ea: MID(A1,FIND(" ",A1,1),LEN(A1))
or: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1,0,""),9,""),8,""),7,""),6,""),5,""), 4,""),3,""),2,""),1,"")

gebobs · #3 01-11-2016, 06:34 AM

=value(left(A1,find(" ",A1)-1))

=right(A1,len(A1)-find(" ",A1))

pero978 · #4 01-12-2016, 12:38 AM

what about text to column or flashfill (if excel 2013)?

gebobs · #5 01-12-2016, 07:41 AM

Quote:

Originally Posted by pero978

what about text to column or flashfill (if excel 2013)?

Ah yes, text to column. Forgot about that gem. I'm so used to doing it manually.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Strange Characters appear when selecting SHOW ALL NON PRINTING CHARACTERS	ann Amber	Word	1	08-01-2015 08:06 PM
Assign the value of a cell as a cell reference of another cell in Excel 2010 - How to?	bharathkumarst	Excel	7	10-13-2014 10:25 AM
Word VBA: Cannot Edit Range (Delete characters except the first in a table cell)	tinfanide	Word VBA	3	04-27-2012 09:48 AM
Junk characters (box-like characters) in Word file	Sashikala	Word	1	04-20-2010 02:03 PM
Removing data between two characters	Voodoo Child	Excel	1	11-26-2009 01:39 PM