Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract specific string
Hi all,
I need extract a specific string and my cells may contains many alphanumeric data In this example I need Extract only S00059997 and S00075671 the LEN is 9 all the time. Thanks for your Help Data_Account ---------------- ANY-NOTES ANY---NAMES S00059997 ANY-NOTES S00075671 Dante |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract specific string
I think you may have to tell us more about what you are trying to do and
what you have to work with. First, I'm assuming each item in the list you showed is on a separate row. Do the strings you are trying to extract always start with a single letter? If yes, I'm guessing it is not always an "S", is it? Again, if so, then will only digits follow it? In other words, are you looking for a single letter followed by eight digits? After you answer the above questions, here is the most important question... where did you want these values "extracted" to? An array in VB code? Another column? Somewhere else? -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... Hi all, I need extract a specific string and my cells may contains many alphanumeric data In this example I need Extract only S00059997 and S00075671 the LEN is 9 all the time. Thanks for your Help Data_Account ---------------- ANY-NOTES ANY---NAMES S00059997 ANY-NOTES S00075671 Dante |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract specific string
The string start always with "S" and finished with 8 digits and a VB function
will be help me so much. Thanks Dante "Rick Rothstein" wrote: I think you may have to tell us more about what you are trying to do and what you have to work with. First, I'm assuming each item in the list you showed is on a separate row. Do the strings you are trying to extract always start with a single letter? If yes, I'm guessing it is not always an "S", is it? Again, if so, then will only digits follow it? In other words, are you looking for a single letter followed by eight digits? After you answer the above questions, here is the most important question... where did you want these values "extracted" to? An array in VB code? Another column? Somewhere else? -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... Hi all, I need extract a specific string and my cells may contains many alphanumeric data In this example I need Extract only S00059997 and S00075671 the LEN is 9 all the time. Thanks for your Help Data_Account ---------------- ANY-NOTES ANY---NAMES S00059997 ANY-NOTES S00075671 Dante . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract specific string
Unfortunately, you didn't answer all the questions I asked, so I am still
not sure how to reply to you. Are the items you showed in your list in a column, one item per row? Where do you want the extracted strings to go... an array inside of VB code, another column or somewhere else? -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... The string start always with "S" and finished with 8 digits and a VB function will be help me so much. Thanks Dante "Rick Rothstein" wrote: I think you may have to tell us more about what you are trying to do and what you have to work with. First, I'm assuming each item in the list you showed is on a separate row. Do the strings you are trying to extract always start with a single letter? If yes, I'm guessing it is not always an "S", is it? Again, if so, then will only digits follow it? In other words, are you looking for a single letter followed by eight digits? After you answer the above questions, here is the most important question... where did you want these values "extracted" to? An array in VB code? Another column? Somewhere else? -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... Hi all, I need extract a specific string and my cells may contains many alphanumeric data In this example I need Extract only S00059997 and S00075671 the LEN is 9 all the time. Thanks for your Help Data_Account ---------------- ANY-NOTES ANY---NAMES S00059997 ANY-NOTES S00075671 Dante . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract specific string
- Are the items you showed in your list in a column, one item per row? Yes - Where do you want the extracted strings to go... an array inside of VB code, another column or somewhere else? Basically I need a VB function, for example: if I have in colunmn A a row S00256325 with the function extract this in column B any other alphanumeric data column B is blank ColumnA ColumnB I see S00256325 =Myextractfunction(A2) S00256325 AnyotherData =Myextractfunction(A2) Blank Thanks so much for your time! Dante "Rick Rothstein" wrote: Unfortunately, you didn't answer all the questions I asked, so I am still not sure how to reply to you. Are the items you showed in your list in a column, one item per row? Where do you want the extracted strings to go... an array inside of VB code, another column or somewhere else? -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... The string start always with "S" and finished with 8 digits and a VB function will be help me so much. Thanks Dante "Rick Rothstein" wrote: I think you may have to tell us more about what you are trying to do and what you have to work with. First, I'm assuming each item in the list you showed is on a separate row. Do the strings you are trying to extract always start with a single letter? If yes, I'm guessing it is not always an "S", is it? Again, if so, then will only digits follow it? In other words, are you looking for a single letter followed by eight digits? After you answer the above questions, here is the most important question... where did you want these values "extracted" to? An array in VB code? Another column? Somewhere else? -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... Hi all, I need extract a specific string and my cells may contains many alphanumeric data In this example I need Extract only S00059997 and S00075671 the LEN is 9 all the time. Thanks for your Help Data_Account ---------------- ANY-NOTES ANY---NAMES S00059997 ANY-NOTES S00075671 Dante . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract specific string
Assuming your data starts in A2 (with A1 being a header), put this formula
in B2 and copy it down as far as you need to... =IF(AND(LEN(A2)=9,LEFT(A2)="S",ISNUMBER(--MID(A2,2,8))),A2,"") -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... - Are the items you showed in your list in a column, one item per row? Yes - Where do you want the extracted strings to go... an array inside of VB code, another column or somewhere else? Basically I need a VB function, for example: if I have in colunmn A a row S00256325 with the function extract this in column B any other alphanumeric data column B is blank ColumnA ColumnB I see S00256325 =Myextractfunction(A2) S00256325 AnyotherData =Myextractfunction(A2) Blank Thanks so much for your time! Dante "Rick Rothstein" wrote: Unfortunately, you didn't answer all the questions I asked, so I am still not sure how to reply to you. Are the items you showed in your list in a column, one item per row? Where do you want the extracted strings to go... an array inside of VB code, another column or somewhere else? -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... The string start always with "S" and finished with 8 digits and a VB function will be help me so much. Thanks Dante "Rick Rothstein" wrote: I think you may have to tell us more about what you are trying to do and what you have to work with. First, I'm assuming each item in the list you showed is on a separate row. Do the strings you are trying to extract always start with a single letter? If yes, I'm guessing it is not always an "S", is it? Again, if so, then will only digits follow it? In other words, are you looking for a single letter followed by eight digits? After you answer the above questions, here is the most important question... where did you want these values "extracted" to? An array in VB code? Another column? Somewhere else? -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... Hi all, I need extract a specific string and my cells may contains many alphanumeric data In this example I need Extract only S00059997 and S00075671 the LEN is 9 all the time. Thanks for your Help Data_Account ---------------- ANY-NOTES ANY---NAMES S00059997 ANY-NOTES S00075671 Dante . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract specific string
Thanks for your help, very appreciated!
"Rick Rothstein" wrote: Assuming your data starts in A2 (with A1 being a header), put this formula in B2 and copy it down as far as you need to... =IF(AND(LEN(A2)=9,LEFT(A2)="S",ISNUMBER(--MID(A2,2,8))),A2,"") -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... - Are the items you showed in your list in a column, one item per row? Yes - Where do you want the extracted strings to go... an array inside of VB code, another column or somewhere else? Basically I need a VB function, for example: if I have in colunmn A a row S00256325 with the function extract this in column B any other alphanumeric data column B is blank ColumnA ColumnB I see S00256325 =Myextractfunction(A2) S00256325 AnyotherData =Myextractfunction(A2) Blank Thanks so much for your time! Dante "Rick Rothstein" wrote: Unfortunately, you didn't answer all the questions I asked, so I am still not sure how to reply to you. Are the items you showed in your list in a column, one item per row? Where do you want the extracted strings to go... an array inside of VB code, another column or somewhere else? -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... The string start always with "S" and finished with 8 digits and a VB function will be help me so much. Thanks Dante "Rick Rothstein" wrote: I think you may have to tell us more about what you are trying to do and what you have to work with. First, I'm assuming each item in the list you showed is on a separate row. Do the strings you are trying to extract always start with a single letter? If yes, I'm guessing it is not always an "S", is it? Again, if so, then will only digits follow it? In other words, are you looking for a single letter followed by eight digits? After you answer the above questions, here is the most important question... where did you want these values "extracted" to? An array in VB code? Another column? Somewhere else? -- Rick (MVP - Excel) "Dante Huapaya" wrote in message ... Hi all, I need extract a specific string and my cells may contains many alphanumeric data In this example I need Extract only S00059997 and S00075671 the LEN is 9 all the time. Thanks for your Help Data_Account ---------------- ANY-NOTES ANY---NAMES S00059997 ANY-NOTES S00075671 Dante . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to extract specific text from a string of characters | Excel Worksheet Functions | |||
Extract string after specific characters | Excel Programming | |||
Formula to extract a specific word from text string | Excel Worksheet Functions | |||
Extract specific value from a long text string | Excel Worksheet Functions | |||
extract string | Excel Programming |