Function FirstX(MyText As String, X As Long)
FirstX = UCase(Left(MyText, X) & Left(Split(MyText, " ")(UBound(Split(MyText, " "))), X))
End Function
Excel 2010 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Tire Rack | TIRRAC | TIRRAC | TIRRAC | ||
2 | William J Smith | WILJ S | WILSMI | WILSMI | ||
3 | Harold Hold | HARHOL | HARHOL | HARHOL | ||
4 | Edger J Hoover | EDGJ H | EDGHOO | EDGHOO | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | =UPPER(LEFT(A1,3) & MID(A1,FIND(" ",A1)+1,3)) | |
C1 | =UPPER(LEFT(A1,3) & LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),3)) | |
D1 | =FirstX(A1,3) |
@BladeAnd this is how you do it with the REPT instead of the UDF:
=UPPER(LEFT(A1,3) & LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),3))
Here are some results to show what I mean:
Excel 2010
A B C D 1 Tire Rack TIRRAC TIRRAC TIRRAC 2 William J Smith WILJ S WILSMI WILSMI 3 Harold Hold HARHOL HARHOL HARHOL 4 Edger J Hoover EDGJ H EDGHOO EDGHOO Sheet1
Cell Formulas Range Formula B1 =UPPER(LEFT(A1,3) & MID(A1,FIND(" ",A1)+1,3)) C1 =UPPER(LEFT(A1,3) & LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),3)) D1 =FirstX(A1,3)