Tuesday, January 26, 2010

The fastest way to parse text in Excel
An Excel user defined function to extract characters from text
manipulating strings in your data.
Strings and Manipulations
exceltips.com
separate two words from a cell in two cells
How to separate the last word in MS Excel?
theres probably an easier way.
is this a good sample? in other words will there always be 2 commas?
copy&paste this in B1

=RIGHT(A1,LEN(A1)-FIND(",",A1, FIND(",",A1,1)+1)-1)

- it finds the 1st comma =5
- it starts at the 1st comma (5th character) and finds the 2nd comma =19
- then it takes the full length and subtracts ou the 19 and you are left with the right side characters.

if you have different data, you can work this this as a start.

edit-
lol thats what i thought.
copy&paste this..

=RIGHT(A1,LEN(A1)-FIND(CHAR(1), SUBSTITUTE(A1,",", CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))

we can do this the difficult (but more universal) way...this should work.
char(1) is the enter key at the end.
the substitute() temporarily replaces the enter key with a comma, and works backwards.

oh, and whats the deal with that Mathura one all by itself? it has no commas. use this

=IF(ISERR( FIND(",",A1,1)),A1,RIGHT(A1,LEN(A1)-FIND… SUBSTITUTE(A1,",", CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))))
Rearranging Data in Columns
Strip off last 6 characters of cell
To extract all characters from a cell, except the last six characters...

=LEFT(A1,LEN(A1)-6)

To extract the last six characters of a cells...

=RIGHT(A1,6)
http://spreadsheetpage.com/index.php/tip/the_versatile_split_function/
Splitting Text to Multiple Cells

No comments:

Post a Comment