delete certain text from a cell

[kakster]

Greetings,

i have an excel sheet with about 2000 entries in it.
i only want to delete certain text in a column.

For example,

in a column i have cells with the following format:

First Name_Last Name_Middle Initial_(some info in here)

under scores are used here to represent spaces.

I want to delete everything except what's in parenthesis.

How do i do this?

Thanks,

Nic

 

[Bernie Deitrick]

Nick,

In another column, use a formula like this, to just get the text inside the
parens (without the parens)

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

Or this, to keep the parens:
=MID(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1)

Copy the formula down to match your data, then copy and pastespecial values
over the formulas, and delete your original column.

HTH,
Bernie
MS Excel MVP

 

[kakster]

THANK YOU SOOOOO MUCH!

 

[kakster]

Another question for you:

how do i delete the phrase "#VALUE!" in the cells that did not return
value? I tried the replace operation however excel doesn't see th
"#VALUE!" phrase in the replace search.

another challenge:
is there a way to extract only the middle initial?
for example i have a cell that looks like this:

ANTHONY E. (LISW)

i want to extract only the letter before the period. this of cours
applies to many many cells.

Can this be done?

Thank you for your time,

Nic

 

[Bernie Deitrick]

Nick,

Part 1:

Use a formula like
=IF(ISERROR(old formula),"",old formula)
where old formula is the formula that I sent you (without the = sign).

Part 2:

=MID(A1,FIND(".",A1)-1,1)

but if not all have a period ofter an initial, then you can use
=IF(ISERROR(MID(A1,FIND(".",A1)-1,1)),"",MID(A1,FIND(".",A1)-1,1))

HTH,
Bernie
MS Excel MVP

 

[kakster]

Thanks again for your help!

part 1 worked excellent.

Unfortunately the second formula:

=IF(ISERROR(MID(A1,FIND(".",A1)-1,1)),"",MID(A1,FIND(".",A1)-1,1))

only returns initials with a period after them and excludes th
initials that do not have a period after them. Maybe something is lef
out of the formula?

Thank you for your time,

Nic

 

[Bernie Deitrick]

Nick,

How else would an initial be indicated, if not with a period? Post some
samples of any variation that you would want to extract.

HTH,
Bernie
MS Excel MVP

 

[kakster]

For example,


in column B:

GAYLE S.
LAURA M

i want the middle initial for each person extracted to another column
for example
column C.

and if possible have the formula delete the initial in column B so tha
only the first
name remains.

in the example above there is a middle initial with a period (.) afte
it and a middle initial
with no period after it - both cases occur throughout the data in m
spreadsheet so the
formula would have to be able to pick out both cases.

Thanks for your time,

Nic

 

[Bernie Deitrick]

Nick,

In C1:

=IF(ISERROR(MID(A1,FIND(".",A1)-1,1)),MID(A1,FIND("
",A1)+1,1),MID(A1,FIND(".",A1)-1,1))

and if possible have the formula delete the initial in column B so that
only the first
name remains.

It's not. You can use a macro, but not formulas. You could get just the
first name in column D. In D1:

=LEFT(A1,FIND(" ",A1)-1)

HTH,
Bernie
MS Excel MVP

 

[AlfD]

Hi!

Try this on Gayle S. and Laura M (no dot).

In B1: =IF(RIGHT(A1,1)<>".",LEFT(A1,LEN(A1)-2),LEFT(A1,LEN(A1)-3))

In C1: =IF(RIGHT(A1,1)<>".",RIGHT(A1),MID(A1,LEN(A1)-2,1))

Al

 

[2rrs]

Have you tried using the Data>text to columns feature?
It will handle the problem you posed quite well without the use of formulas.