Code to get text from part of a cell

[Keenman]

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...

[Biff]

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 Edit>Copy
Then Edit>Paste Special>Values

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

Biff,

Worked great!!! Thanks...

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 Edit>Copy
Then Edit>Paste Special>Values

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

You're welcome. Thanks for the feedback!

Biff

"Keenman" <[email protected]> wrote in
message news:[email protected]...
>
> 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 Edit>Copy
>> Then Edit>Paste Special>Values
>>
>> 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" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>