text formula needed

  • Thread starter Thread starter Dave W
  • Start date Start date
D

Dave W

I am having one of those days
I can't seem to sort through what I need to return the
first letter of each word.

as you see I'm getting a space before the c whic I don't
want.

any help would be appreciated


col 1 col 2
cabinet types Code

normal base cab nb c

=MID(B303,1,1)&MID(B303,FIND(" ",B303)+1,1)&MID(B303,FIND
(" ",B303,(FIND(" ",B303)+1)),2)
 
I think you forgot to increment in your second mid...
you increment+1 for you find function, but then you return
the position of your second " " what you want to do is
increment the mid +1 also and return only one:
=MID(B303,1,1)&MID(B303,FIND(" ",B303)+1,1)&MID(B303,FIND
(" ",B303,(FIND(" ",B303)+1))+1,1)
Click to expand...
hopefully that solves it...
best
 
Thank you for seeing that
but as it turns out I need another approach since some of
the words in my list are less than 3 words long.

any other sugestions would be a help today
 
The TRIM function removes all leading abd trailing spaces
so if the result of your formula is:

" cab " and you use trim on it, you will have "cab"

If the result of your forlua is what you are expecting
exept for the spaces Try:

=TRIM(MID(B303,1,1)&MID(B303,FIND(" ",B303)+1,1)&MID
(B303,FIND(" ",B303,(FIND(" ",B303)+1)),2))

Cheers
Juan
 
You have to make your own formula. try something like:

Function firstLet(text) As String
Dim TextLen As Integer
Dim i As Integer
TextLen = Len(text)
firstLet = Left(text, 1)
For i = 1 To TextLen Step 1
If Mid(text, i, 1) = " " Then
firstLet = firstLet & Mid(text, i + 1, 1)
End If
Next i
End Function

best
Mathias
 
After re-reading your post, this two formulas work:

=TRIM(MID(B303,1,1))&TRIM(MID(B303,FIND(" ",B303)+1,1))
&TRIM(MID(B303,FIND(" ",B303,(FIND(" ",B303)+1)),2))

=MID(B303,1,1)&MID(B303,FIND(" ",B303)+1,1)&MID(B303,FIND
(" ",B303,(FIND(" ",B303)+1))+1,1)

Cheers
Juan
 
Back
Top