|
How do you trim characters?
Hi there! This is my first post, so please have mercy on my soul. :rolleyes: In one cell I have a character string, e.g. 1011-1 I want to extract the 3rd and 4th character to a new cell, i.e. "11" above, as a number. I was thinking of using a trim-type function to remove the first two characters and everything after (and including) the hypen. Problem: the trim function seems to only remove spaces. I'd like to do this in the little fx window rather than using anything fancy like VBA. Thanks for any help you can provide. :) -- Tommi ------------------------------------------------------------------------ Tommi's Profile: http://www.excelforum.com/member.php...o&userid=30942 View this thread: http://www.excelforum.com/showthread...hreadid=506143 |
How do you trim characters?
Try this, with your data in A1 =MID(A1,3,2)+0 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=506143 |
How do you trim characters?
Hi Tommi
With the value containing the string try =MID(A1,3,2) The 3 tells the Mid function to start at the 3rd character position The 2 tells it to take 2 characters. If you want the resulting 11 to be numeric instead of text, then precede the formula with the double unary minus -- which will coerce the text value to a number =--MID(A1,3,2) -- Regards Roger Govier "Tommi" wrote in message ... Hi there! This is my first post, so please have mercy on my soul. :rolleyes: In one cell I have a character string, e.g. 1011-1 I want to extract the 3rd and 4th character to a new cell, i.e. "11" above, as a number. I was thinking of using a trim-type function to remove the first two characters and everything after (and including) the hypen. Problem: the trim function seems to only remove spaces. I'd like to do this in the little fx window rather than using anything fancy like VBA. Thanks for any help you can provide. :) -- Tommi ------------------------------------------------------------------------ Tommi's Profile: http://www.excelforum.com/member.php...o&userid=30942 View this thread: http://www.excelforum.com/showthread...hreadid=506143 |
How do you trim characters?
Is your data all the same length?
If so then just copy your column to the next column, select all the data and do data / text to columns / fixed width, put in breaks after the 2nd and 4th characters, hit next and then click on first and last columns in the dialog box and choose 'do not import' Hit Ok and you are done. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Tommi" wrote in message ... Hi there! This is my first post, so please have mercy on my soul. :rolleyes: In one cell I have a character string, e.g. 1011-1 I want to extract the 3rd and 4th character to a new cell, i.e. "11" above, as a number. I was thinking of using a trim-type function to remove the first two characters and everything after (and including) the hypen. Problem: the trim function seems to only remove spaces. I'd like to do this in the little fx window rather than using anything fancy like VBA. Thanks for any help you can provide. :) -- Tommi ------------------------------------------------------------------------ Tommi's Profile: http://www.excelforum.com/member.php...o&userid=30942 View this thread: http://www.excelforum.com/showthread...hreadid=506143 |
How do you trim characters?
With the value containing the string try
That should have read With A1 containing the string, try =MID(A1,3,2) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Tommi With the value containing the string try =MID(A1,3,2) The 3 tells the Mid function to start at the 3rd character position The 2 tells it to take 2 characters. If you want the resulting 11 to be numeric instead of text, then precede the formula with the double unary minus -- which will coerce the text value to a number =--MID(A1,3,2) -- Regards Roger Govier "Tommi" wrote in message ... Hi there! This is my first post, so please have mercy on my soul. :rolleyes: In one cell I have a character string, e.g. 1011-1 I want to extract the 3rd and 4th character to a new cell, i.e. "11" above, as a number. I was thinking of using a trim-type function to remove the first two characters and everything after (and including) the hypen. Problem: the trim function seems to only remove spaces. I'd like to do this in the little fx window rather than using anything fancy like VBA. Thanks for any help you can provide. :) -- Tommi ------------------------------------------------------------------------ Tommi's Profile: http://www.excelforum.com/member.php...o&userid=30942 View this thread: http://www.excelforum.com/showthread...hreadid=506143 |
How do you trim characters?
Wow, thanks guys. More than awesome! -- Tommi ------------------------------------------------------------------------ Tommi's Profile: http://www.excelforum.com/member.php...o&userid=30942 View this thread: http://www.excelforum.com/showthread...hreadid=506143 |
| All times are GMT +1. The time now is 01:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com