Simply Formula

[Allison]

Is there a simple formula approach to trimming numbers?
Our full Grant Number in one system appears as
Example: 1 F32 AI540542003A1 and in the other system it
appears 54054

Also, can you explain the formula below?

=IF(H30<>"CA",TRIM(F30)&" "&TRIM(G30)&" "&TRIM(H30)&RIGHT
(LEFT(TRIM(B30),14),5)&TRIM(D30)&TRIM(K30),TRIM(F30)
&" "&TRIM(G30)&" "&TRIM(H30)&RIGHT(LEFT(TRIM(B30),14),6)
&TRIM(D30)&TRIM(K30))

Thanks Again for all your Help!!!

 

[Vince Owens]

Hi,

For trimming numbers there are a number of functions ie,
=INT(yournumber) would round up to a whole number. (Thats
an Integer formula)

As for your formula below, from what i can tell it is
basically using the TRIM function to remove spaces between
letters in a word.

Hope that helps

Vince

WWW.EXCELAID.COM

 

[Michael J. Malinsky]

Assuming your long form grant number is always in the same format and
length, you can use the following formula to trim away the unwanted stuff:

=MID(A5,9,5)

Insofar as the lengthy formula you want to understand, I'll give it a shot.

The IF statement says the if the value in cell H30 does not equal the string
"CA", then do the part of the formula:

TRIM(F30)&" "&TRIM(G30)&"
"&TRIM(H30)&RIGHT(LEFT(TRIM(B30),14),5)&TRIM(D30)&TRIM(K30)

else do this part of the formula:

TRIM(F30)&" "&TRIM(G30)&"
"&TRIM(H30)&RIGHT(LEFT(TRIM(B30),14),6)&TRIM(D30)&TRIM(K30)

These two parts do essentially the same thing. First, the TRIM function
removes all leading and trailing spaces and leaves only single spaces
between words. So if, for example, you had " ABC ", the formula:

=TRIM(" ABC ")

would return "ABC" (note no spaces inside the quotes). TRIM makes strings
neat when using them in other formulas or just for appearance.

The part of the formula that says this:

RIGHT(LEFT(TRIM(B30),14),5)

first TRIMs the value in B30, then takes the LEFT 14 characters of the
result. Then, the RIGHT 5 characters are taken from that result. The
second part of the IF formula does the same thing except it takes the RIGHT
6 characters instead of 5.

I hope I made this understandable for you, if not, post back, or maybe
someone else has taken a shot at it as well.

HTH
Mike

 

[Dave Smith]

If you always need to get 5 characters starting at character 9 in the longer
string you can use:

=MID(A1,9,5)

Example: if A1 = "abcdefghijklmnopqrstuvwxyz", the formula result is
"ijklm".

The formula concatenates several cell values using one of two slightly
different subformulas . It uses TRIM to remove spaces at the beginning and
end of the values. It extracts a substring from B30 using Right and Left
(clearer would be to use MID as above). The difference between the
subformulas is that the first (used when H30 is not "CA") extracts
characters 10-14 from TRIM(B30) while the second extracts characters 9-14.

=IF(H30<>"CA", TRIM(G30)&" "&TRIM(G30)&" "&TRIM(H30)&MID(TRIM(B30), 14,
5)&TRIM(D30)&TRIM(K30),
TRIM(G30)&" "&TRIM(G30)&"
"&TRIM(H30)&MID(TRIM(B30), 14, 6)&TRIM(D30)&TRIM(K30))

HTH

-Dave