delete characters from the left

[pukiamak]

Hello Guys,

I have sets of data in excel which like the following:

1) good morning sir
2) good afternoon sir
3) good night sir
4)...
5)...

and goes on

I wish to delete the number and the bracket following it. In other words, I wish to delete 1),2),3) and so on.How can i do that?

Thx a lot for the help,
Nic

 

[PHV]

newData = Mid(oldData, InStr(oldData, ")") + 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pukiamak]

Hello PHV,

Thanks a lot for the quick reply. However I do have like 500++ sets of row. I tested it and it seems that your code doesn't work out. Do you know why?

Thanks a lot,
Nic

 

[mintjulep]

Because PHV's solution was not necessarilly intended to be "code". It provides a suggestion to the Excel functions that do what you need to do.

For example, if your "oldData" starts in cell G1 you might want to create a new column H. Then enter the following formula in cell H1:

Mid(G1, InStr(G1, ")") + 1)

And copy that down for however many rows you have.

A "paste special" would then be needed to obtain "pure" text.

 

[pukiamak]

thx guys for the reply. i solve the problem.

 

[firefytr]

..Then enter the following formula in cell H1:

Mid(G1, InStr(G1, ")") + 1)

I don't think that's a recognized formula.. Maybe you mean..

=MID(G1,FIND(" ",G1,1)+1... (you never finished it)

I would just use ..

=RIGHT(A1,LEN(A1)-3)

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[AnotherHiggins]

the problem with using a static '3' is that it won't account for situations when the number is double digits or more.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

Ah yes, good point John. Then the RIGHT along with the FIND should account for all standardized options.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci