Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Keenman
 
Posts: n/a
Default Code to get text from part of a cell


I have a worksheet with data in a column as follows

uid=xvdcfrd,ou=people,dc=xxxxx
uid=sdsdsd,ou=people,dc=xxxxx
uid=fhjetyv,ou=people,dc=xcxcx

Each of the above lines is in a single cell in the column. I need to
start at the top cell and retrieve the text following the first = sign
and grab the text to the first comma (in cell one in the above example,
I want xvdcfrd). After retrieving the text I want to copy/move it to
another column (If I delete the formula, I want to keep the retrieved
data unless there is a way to write the data that I retrieve in the
same column that I am performing this function on). I need the code to
loop through the entire column and grab that data from each cell.

Thanks in advance...


--
Keenman
------------------------------------------------------------------------
Keenman's Profile: http://www.excelforum.com/member.php...o&userid=14220
View this thread: http://www.excelforum.com/showthread...hreadid=536246

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Code to get text from part of a cell

Hi!

One way: (assuming every entry starts with uid= and has at least one comma):

=LEFT(SUBSTITUTE(A1,LEFT(A1,4),""),FIND(",",A1)-5)

Copy down as needed.

Not sure what you intend to do about deleting the formulas but you might try
this:

Select the range of formulas
Goto EditCopy
Then EditPaste SpecialValues

This will convert the formulas to constants.

Then you could delete the original data BUT before you do that make sure the
formulas extracted the correct data!

Biff

"Keenman" wrote in
message ...

I have a worksheet with data in a column as follows

uid=xvdcfrd,ou=people,dc=xxxxx
uid=sdsdsd,ou=people,dc=xxxxx
uid=fhjetyv,ou=people,dc=xcxcx

Each of the above lines is in a single cell in the column. I need to
start at the top cell and retrieve the text following the first = sign
and grab the text to the first comma (in cell one in the above example,
I want xvdcfrd). After retrieving the text I want to copy/move it to
another column (If I delete the formula, I want to keep the retrieved
data unless there is a way to write the data that I retrieve in the
same column that I am performing this function on). I need the code to
loop through the entire column and grab that data from each cell.

Thanks in advance...


--
Keenman
------------------------------------------------------------------------
Keenman's Profile:
http://www.excelforum.com/member.php...o&userid=14220
View this thread: http://www.excelforum.com/showthread...hreadid=536246



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Keenman
 
Posts: n/a
Default Code to get text from part of a cell


Biff,

Worked great!!! Thanks...


Biff Wrote:
Hi!

One way: (assuming every entry starts with uid= and has at least one
comma):

=LEFT(SUBSTITUTE(A1,LEFT(A1,4),""),FIND(",",A1)-5)

Copy down as needed.

Not sure what you intend to do about deleting the formulas but you
might try
this:

Select the range of formulas
Goto EditCopy
Then EditPaste SpecialValues

This will convert the formulas to constants.

Then you could delete the original data BUT before you do that make
sure the
formulas extracted the correct data!

Biff

"Keenman" wrote
in
message ...

I have a worksheet with data in a column as follows

uid=xvdcfrd,ou=people,dc=xxxxx
uid=sdsdsd,ou=people,dc=xxxxx
uid=fhjetyv,ou=people,dc=xcxcx

Each of the above lines is in a single cell in the column. I need

to
start at the top cell and retrieve the text following the first =

sign
and grab the text to the first comma (in cell one in the above

example,
I want xvdcfrd). After retrieving the text I want to copy/move it

to
another column (If I delete the formula, I want to keep the

retrieved
data unless there is a way to write the data that I retrieve in the
same column that I am performing this function on). I need the code

to
loop through the entire column and grab that data from each cell.

Thanks in advance...


--
Keenman

------------------------------------------------------------------------
Keenman's Profile:
http://www.excelforum.com/member.php...o&userid=14220
View this thread:

http://www.excelforum.com/showthread...hreadid=536246



--
Keenman
------------------------------------------------------------------------
Keenman's Profile: http://www.excelforum.com/member.php...o&userid=14220
View this thread: http://www.excelforum.com/showthread...hreadid=536246

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Code to get text from part of a cell

You're welcome. Thanks for the feedback!

Biff

"Keenman" wrote in
message ...

Biff,

Worked great!!! Thanks...


Biff Wrote:
Hi!

One way: (assuming every entry starts with uid= and has at least one
comma):

=LEFT(SUBSTITUTE(A1,LEFT(A1,4),""),FIND(",",A1)-5)

Copy down as needed.

Not sure what you intend to do about deleting the formulas but you
might try
this:

Select the range of formulas
Goto EditCopy
Then EditPaste SpecialValues

This will convert the formulas to constants.

Then you could delete the original data BUT before you do that make
sure the
formulas extracted the correct data!

Biff

"Keenman" wrote
in
message ...

I have a worksheet with data in a column as follows

uid=xvdcfrd,ou=people,dc=xxxxx
uid=sdsdsd,ou=people,dc=xxxxx
uid=fhjetyv,ou=people,dc=xcxcx

Each of the above lines is in a single cell in the column. I need

to
start at the top cell and retrieve the text following the first =

sign
and grab the text to the first comma (in cell one in the above

example,
I want xvdcfrd). After retrieving the text I want to copy/move it

to
another column (If I delete the formula, I want to keep the

retrieved
data unless there is a way to write the data that I retrieve in the
same column that I am performing this function on). I need the code

to
loop through the entire column and grab that data from each cell.

Thanks in advance...


--
Keenman

------------------------------------------------------------------------
Keenman's Profile:
http://www.excelforum.com/member.php...o&userid=14220
View this thread:

http://www.excelforum.com/showthread...hreadid=536246



--
Keenman
------------------------------------------------------------------------
Keenman's Profile:
http://www.excelforum.com/member.php...o&userid=14220
View this thread: http://www.excelforum.com/showthread...hreadid=536246



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
HOW TO KEEP TEXT FROM MOVING OUT OF A CELL? Briguyy Excel Discussion (Misc queries) 2 February 23rd 06 10:04 PM
Can you keep text from one cell showing over the next cell? Sean VandeWall Excel Discussion (Misc queries) 2 February 22nd 06 09:20 PM
Shade cell according to text? Ltat42a Excel Discussion (Misc queries) 0 January 3rd 06 07:37 PM
Text in formula bar is not displaying in cell Mike Excel Discussion (Misc queries) 0 August 29th 05 09:47 PM
Formulas dealing with text data Bagia Excel Worksheet Functions 6 June 20th 05 10:29 PM


All times are GMT +1. The time now is 12:52 PM.

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"