Extract from string

[AndyD]

hi there
If can help with the following would be appreciated
thanks
Andy


****
Example:
Big Long Name Ltd - this has 17 characters
Name Ltd - this has 8 characters

I want to extract a (max) of 10 characters but with each shortened name making sense.

Eg if I take first 10 characters from "Big Long Name Ltd" it gives me "Big Long N". Would be better to use "Big Long" instead.

I guess what Im after is using full words only up to 10 characters

i hope Im being clear

Cheers
Andy

 

[Oaktree]

Probably a better way out there, but this seems to work:

=IF(OR(LEN(A1)<10,MID(A1,11,1)=" "),LEFT(A1,10),LEFT(A1,SEARCH("@",SUBSTITUTE(LEFT(A1,10)," ","@",10-LEN(SUBSTITUTE(LEFT(A1,10)," ",""))))-1))

 

[barry houdini]

Try

=LEFT(A1,IF(FIND(" ",A1&" ")>10,10,LOOKUP(11,FIND(" ",A1&" ",ROW(INDIRECT("1:"&LEN(A1)))))-1))

If the first word in A1 is longer than 10 characters this will just give the first 10 characters, otherwise it does as you ask

 

[Krishnakumar]

=LEFT(A1,IF(FIND(" ",A1&" ")>10,10,LOOKUP(11,FIND(" ",A1&" ",ROW(INDEX(A:A,1):INDEX(A:A,11)))-1)))

 

[jindon]

Hi
UDF If you like
1) Hit Alt + F11
2) [Insert]-[Module] and paste the code there
3) Hit Alt + F11
4) in Cell =CompName(A1,10)

Function CompName(txt As String, limit As Integer) As String
Dim m As Object
With CreateObject("VBScript.RegExp")
   .Pattern = "\S+"
   .Global = True
   For Each m In .execute(txt)
      If Len(CompName & m.value)>limit Then Exit For
      CompName = CompName & m.value & Chr(32)
   Next
End With
CompName = Trim(CompName)
End Function

 

[AndyD]

Hello thanks for all the replies guys.

If I go the UDF route, do I need to set these up all on the pcs using this?

Cheers
Andy

 

[jindon]

Andy

Personally, I don't like Add-Ins..

I would create new workbook and paste all the UDF(if any other)
and save it as sopmething like Function.xlt(template) then distribute it to each PC.
So that anyone can use those functions with it.
Do you know what I mean?