# Extract from string

#### AndyD

##### Active Member
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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))``

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

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

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``````

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

Andy

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?

Replies
4
Views
244
Replies
6
Views
202
Replies
0
Views
214
Replies
2
Views
424
Replies
21
Views
606

1,217,347
Messages
6,136,046
Members
449,983
Latest member
mschaef6

### 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.

### Which adblocker are you using?

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

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