Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 169
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 159
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting text from a string [email protected] Excel Worksheet Functions 8 June 2nd 08 10:09 PM
Extracting text from string Confused Excel Worksheet Functions 4 February 15th 08 04:34 PM
Extracting Numeric Data from a Delimited Text String [email protected] Excel Worksheet Functions 5 February 10th 06 11:29 PM
Extracting from a text string AmyTaylor Excel Worksheet Functions 3 June 24th 05 01:34 PM
extracting data from a text string of varying length andy from maine Excel Discussion (Misc queries) 4 March 28th 05 07:11 PM


All times are GMT +1. The time now is 05:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"