Help - Separating numbers appearing on the right of a text string
I need to separate the number from the right of a text string into its
own cell. For example in the data: "Salaries - Aides / Assistants 3708" "Classroom Stationery & Materials 21" I want to return 3708 and 21 as the value of the formula. Note that there are spaces and the number lengths vary. Does anybody know a formula I can write to extract this floating numeric string which is not a fixed length and at the right of the text string. What this means is that I need to find the position from where the numbers in the string start, and using the MID and LEN functions I can specify this value to get the number extracted into its own cell. Thanks. |
Help - Separating numbers appearing on the right of a text string
Here's one crack at it ..
Assuming the strings are in A1 down Try in B1: =MID(A1,MIN(IF(ISNUMBER(SEARCH({0;1;2;3;4;5;6;7;8; 9},A1)),SEARCH({0;1;2;3;4; 5;6;7;8;9},A1))),LEN(A1)-MIN(IF(ISNUMBER(SEARCH({0;1;2;3;4;5;6;7;8;9},A1)), S EARCH({0;1;2;3;4;5;6;7;8;9},A1)))+1)+0 Copy B1 down -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Faz1" wrote in message oups.com... I need to separate the number from the right of a text string into its own cell. For example in the data: "Salaries - Aides / Assistants 3708" "Classroom Stationery & Materials 21" I want to return 3708 and 21 as the value of the formula. Note that there are spaces and the number lengths vary. Does anybody know a formula I can write to extract this floating numeric string which is not a fixed length and at the right of the text string. What this means is that I need to find the position from where the numbers in the string start, and using the MID and LEN functions I can specify this value to get the number extracted into its own cell. Thanks. |
Help - Separating numbers appearing on the right of a text string
Try Data/Text to columns (Fixed length)!
Regards, Stefi Faz1 ezt *rta: I need to separate the number from the right of a text string into its own cell. For example in the data: "Salaries - Aides / Assistants 3708" "Classroom Stationery & Materials 21" I want to return 3708 and 21 as the value of the formula. Note that there are spaces and the number lengths vary. Does anybody know a formula I can write to extract this floating numeric string which is not a fixed length and at the right of the text string. What this means is that I need to find the position from where the numbers in the string start, and using the MID and LEN functions I can specify this value to get the number extracted into its own cell. Thanks. |
Help - Separating numbers appearing on the right of a text string
Sample construct at:
http://www.savefile.com/files/4680644 Separating_Numbers_From_Text_Faz1_gen.xls -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
All times are GMT +1. The time now is 02:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com