Extract from string

AndyD

Active Member
Joined
Nov 14, 2002
Messages
449
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Probably a better way out there, but this seems to work:

Code:
=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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
=LEFT(A1,IF(FIND(" ",A1&" ")>10,10,LOOKUP(11,FIND(" ",A1&" ",ROW(INDEX(A:A,1):INDEX(A:A,11)))-1)))
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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)
Code:
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

Active Member
Joined
Nov 14, 2002
Messages
449
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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?
 

Forum statistics

Threads
1,136,596
Messages
5,676,712
Members
419,646
Latest member
ReneeDJ73

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top