Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Data from a string of text in a cell
I need to extract all the characters to the right of another character (^).
This (^) character appears in every cell of the column I am working with. It can appear in differnent places within the text of the cell. For example, one cell can have the test "12345^ABC" and another cell can have the tesxt "1234567^ABCDEF". In the first case, I need to extract the "ABC" and the second I need to extract the "ABCDEF". Once extracted I want to put the information into a column next to the original text. Please advise the best way to do this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Data from a string of text in a cell
=MID(cell_ref,FIND("^",cell_ref,1)+1,10)
Assuming the text string you want to return isn't 10 characters long. YOu could easily expand this though. -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "swalker" wrote: I need to extract all the characters to the right of another character (^). This (^) character appears in every cell of the column I am working with. It can appear in differnent places within the text of the cell. For example, one cell can have the test "12345^ABC" and another cell can have the tesxt "1234567^ABCDEF". In the first case, I need to extract the "ABC" and the second I need to extract the "ABCDEF". Once extracted I want to put the information into a column next to the original text. Please advise the best way to do this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Data from a string of text in a cell
On Aug 7, 9:55*pm, swalker wrote:
I need to extract all the characters to the right of another character (^). This (^) character appears in every cell of the column I am working with. It can appear in differnent places within the text of the cell. For example, one cell can have the test "12345^ABC" and another cell can have the tesxt "1234567^ABCDEF". In the first case, I need to extract the "ABC" and the second I need to extract the "ABCDEF". Once extracted I want to put the information into a column next to the original text. Please advise the best way to do this. =RIGHT(A1,LEN(A1)-FIND("^",A1)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Data from a string of text in a cell
If your text is in A1, put this in B1:
=RIGHT(A1,LEN(A1)-FIND("^",A1)) and copy down the column. Hope this helps. Pete On Aug 7, 9:55*pm, swalker wrote: I need to extract all the characters to the right of another character (^). This (^) character appears in every cell of the column I am working with. It can appear in differnent places within the text of the cell. For example, one cell can have the test "12345^ABC" and another cell can have the tesxt "1234567^ABCDEF". In the first case, I need to extract the "ABC" and the second I need to extract the "ABCDEF". Once extracted I want to put the information into a column next to the original text. Please advise the best way to do this. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Data from a string of text in a cell
It is a bit tricky to the right. =RIGHT needs the number of characters
needed to the right or to the end of the string. So needs a more extensive function, but I'm running out of time now. To the left would be easy using in column B =LEFT(A1,SEARCH("^",A1)-1) Cheers, Mark "swalker" wrote in message ... I need to extract all the characters to the right of another character (^). This (^) character appears in every cell of the column I am working with. It can appear in differnent places within the text of the cell. For example, one cell can have the test "12345^ABC" and another cell can have the tesxt "1234567^ABCDEF". In the first case, I need to extract the "ABC" and the second I need to extract the "ABCDEF". Once extracted I want to put the information into a column next to the original text. Please advise the best way to do this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting text from a string | Excel Worksheet Functions | |||
Extracting text from string | Excel Worksheet Functions | |||
Extracting Numeric Data from a Delimited Text String | Excel Worksheet Functions | |||
Extracting from a text string | Excel Worksheet Functions | |||
extracting data from a text string of varying length | Excel Discussion (Misc queries) |