Excel: Remove characters from cells using wildcards

[flashdavies]

Can you anyone help me with this problem please?
I have a column of data like the one below and I would like to remove
the the front part, (x) from the cell, but I don't want to remove the
parts in bracket that come later in the cell, for example
(Queensland).
I tried using Replace (**), and it did remove the first set of
brackets and its contents but unfortunately it also removed the second
set of brackets and its contents.
If it helps in the first set of bracket, (x), x is always a number .
While in the second set always contains a word.

Before:

(1) ABC Far North (Queensland) 0630 News
(2) Townsville SEA FM 0800
(11) ABC Coast FM (Gold & Tweed Coasts) 1400
(1) ABC Far North (Queensland) 0630
(212) ABC Far North (Queensland) 0730


After:

ABC Far North (Queensland) 0630 News
Townsville SEA FM 0800
ABC Coast FM (Gold & Tweed Coasts) 1400
ABC Far North (Queensland) 0630
ABC Far North (Queensland) 0730


Can anyone help me please? I would eternally gratefull.

Harry

 

[Ron Rosenfeld]

Can you anyone help me with this problem please?
I have a column of data like the one below and I would like to remove
the the front part, (x) from the cell, but I don't want to remove the
parts in bracket that come later in the cell, for example
(Queensland).
I tried using Replace (**), and it did remove the first set of
brackets and its contents but unfortunately it also removed the second
set of brackets and its contents.
If it helps in the first set of bracket, (x), x is always a number .
While in the second set always contains a word.

Before:

(1) ABC Far North (Queensland) 0630 News
(2) Townsville SEA FM 0800
(11) ABC Coast FM (Gold & Tweed Coasts) 1400
(1) ABC Far North (Queensland) 0630
(212) ABC Far North (Queensland) 0730


After:

ABC Far North (Queensland) 0630 News
Townsville SEA FM 0800
ABC Coast FM (Gold & Tweed Coasts) 1400
ABC Far North (Queensland) 0630
ABC Far North (Queensland) 0730


Can anyone help me please? I would eternally gratefull.

Harry

ASSUMING that your strings always start with a value in brackets, you could use
this formula;

=TRIM(MID(A1,FIND(")",A1)+1,255))


--ron

 

[flashdavies]

ASSUMING that your strings always start with a value in brackets, you could use
this formula;

=TRIM(MID(A1,FIND(")",A1)+1,255))

--ron- Hide quoted text -

- Show quoted text -

Yes, the strings ALWAYS start with a value in brackets.
Thanks for showing me that. I tried it and it sort of worked - sort -
but only for the first cell: I wasn't sure how to make it apply to the
entire column. But useful as that is to know that is not quite what I
was looking for today (however I am sure I will use that function
sometime).

I realize now that I need to provide more information.
Lets say my column starts at A1 and contiunes to A260, and also, there
is information in columns B, C, D and E, to keep intact. In my
example, each cell value in A represents a medium such as a radio
station, and the values in B represent the ratings, and C represents a
ranking etc.

I want to remove the number in brackets at the begining of the cells
in column A because I wish to sort my data by medium (i.e., column A)
in alphabetical order. Of course, if there is a way to still sort by
alphabetical order by somehow disregarding the information in brackets
that would useful to know as well - actually, extremely useful!

Thanks,
Harry

 

[Dave Peterson]

Temporarily insert a new column B.
Put Ron's formula in A1 and fill it down through B260.

Then select B1:B260
Edit|Copy
Select A1
edit|Paste special|Values

And then delete column B.

 

[flashdavies]

Temporarily insert a new column B.
Put Ron's formula in A1 and fill it down through B260.

Then select B1:B260
Edit|Copy
Select A1
edit|Paste special|Values

And then delete column B.

Gotcha! I should have realized that before. (Its been a long day.)
Thanks very much to both of you.

Harry

 

[Ron Rosenfeld]

Gotcha! I should have realized that before. (Its been a long day.)
Thanks very much to both of you.

Harry

Glad you got it working, Harry. Thanks for the feedback.
--ron

 

[flashdavies]

Glad you got it working, Harry. Thanks for the feedback.
--ron

I am just a beginner with Excel and it took a little while to
understand how that formula works; but, now that I do, I have realized
that it can be, for my purposes, simplified a little to this:
MID(A2,FIND(")",A2)+2,255)

Thanks,
Harry

 

[Ron Rosenfeld]

I am just a beginner with Excel and it took a little while to
understand how that formula works; but, now that I do, I have realized
that it can be, for my purposes, simplified a little to this:
MID(A2,FIND(")",A2)+2,255)

Thanks,
Harry

That works so long as there is always one, and only one, space between the ")"
and the rest of the string.
--ron

 

[flashdavies]

That works so long as there is always one, and only one, space between the ")"
and the rest of the string.
--ron

Yes that is true. And in my example it is not really clear. I guess
TRIM makes sure that it is neat and tidy. Often when data is
imported from another program as it would have been in my example, it
isn't.