Forum Home
Press F1
 
Thread ID: 22768 2002-07-30 01:45:00 Excel Formula Donald M (942) Press F1
Post ID Timestamp Content User
66636 2002-07-30 01:45:00 I have a list in Excel and I want to remove some of the characters eg;

BF4/00 I want it to be 40
BF12/00 " 120
BF 54/00 " 540

So I want to remove the letters BF, the slash and one of the 0's.
Is there a fornula for remove characters?

Hope this makes sense. I have Excell 2000
Thank you. 		Donald M (942)
66637 2002-07-30 02:15:00 Tools-Options-View
tick formulas

Excel will then display the formulas and not the results.

Use find and replace on the selection (find XX, replace with <nothing>) as an example.

Then tools-options-view and put it back the way it was 		godfather (25)
66638 2002-07-30 02:16:00 Hi Donald, yes you can remove characters by formulas Left, Right and Mid. It can get quite complicated if the data isnt in a coinsistent format as these functions work by counting a number of characters from a certain position in the string.

as an example, your first example
BF4/00 I want it to be 40

the formula would be as follow if BF4/00 is in cell a1
=MID(A1,3,1)& RIGHT(A1,2)

Unfortunately your next example has BF12/00 = 120 so it takes a bit more effort.

To work this out you need to find what is consistent with the data - does it always have a BF then the number you want then a / ? If so, we could be in business because we can use a FIND formula to find the F letter and the / and this will help with those mid functions. Also, does 00 always follow the / ? If so, we could just use a mid function and multiply the result by 10.

Let me know about the above questions and I will try and write something that will do the trick for you :-)

cheers
Parry 		parry (27)
66639 2002-07-30 02:30:00 Good one GF. Trust me to over complicate it :-) Also, I wasnt thinking and you probably meant the numbers after the "/" are decimals (ie cents?)

If so then
1) replace bf with nothing
2) replace / with .
3) change format of cells to currency (or a number)

cheers
Parry 		parry (27)
66640 2002-07-30 04:38:00 You guys are amazing! It all worked, thanks a million.

I should give you some back ground, I am a sheep farmer and I have 600 stud sheep that I record on a preformance recording programme. Every sheep has an indivudal number that is in several parts: BF12/00 - BF is the farm where it was born in our case "Bellfield" the 12 is the sheeps number for the year and the 00 is the year it was born 2000. Now comes the interesting part I can export the data into Excel so I can order the sheep tags but we dont need the BF bit and the Tags are better to have less characters on them so we put on 120 - which realy means No. 12 from year 2000.

Any way you have made a major job for me, into a five minuet task!

Thanks so much, I am amazed at the knowledge that some people have and are so willing to share with others. 		Donald M (942)
1