Separate characters in formula

I would like a formula that will separate out the letters and numbers form input into one cell. Say cell "A!" has "S30G in it. I would like cell A2 to separate out the "S" and give that answer only in cell A2 and in cell A3 I would like the 30 number to be the number that is given as an answer in cell A3 and in cell A4 I ould like the G as the answer. There will always be one letter in front, 2 numbers in middle and one letter following i.e. "S#)G" or "D45B" .

Thanks Bill
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

put the following formulae in respective cells




hope this helps.
WilliamKronmillerAuthor Commented:
My  formula would  be something to the affect =sum(if(A1="S",B5)).
How would I enter this in my formula?
you are summing just one cell. do you have a range to sum?


what do you want to sum and also what if the first character is not S?
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

WilliamKronmillerAuthor Commented:
If A1 is "S" then I would have a fomula to compute the answer based on it being S and ithat formula would alos use the 30 (or whatever number was in there) to determine the answer also. I could have used a bunch of If(or compinations to accomplish this but then I would be limited to the number of numbers I could use. Originall I was using only 30, 45, and 60 and they represent degrees. It is a wleding calulation formula and the S stands for single groove weld and 30 would be the degrees and G would be back gouged. I would incorporate all these three in the same formula but they amy all vary.

If A1 equals S and A1 also equals "G" and the angle in A1 is 33 degrees what is the volumn of that area?

I will have to actually get deeper into writting a formula to get it accurate but one more little bit of help will probably get me there for now.
Thank you, Bill
can you upload the spreadsheet? it will be easier to populate that with the required formulae. Thanks.

I guess your volume formula will depend upon the weld type and whether is is gouged and then the formula itself will use the degrees to calculate the volume.
WilliamKronmillerAuthor Commented:
I wish to award the points. This did help and answered my question. Please award the points.

Thanks again for the prompt answer.
WilliamKronmillerAuthor Commented:
I wish to award the points 500 because this was an answer that will work. I am sorry but I think I pushed the wrong buttons.

Please award the points.

Thanks bill

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WilliamKronmillerAuthor Commented:
Thank you. Got it to work last night and now for a lttle creative work on my side. I'll be back. :)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.