Discussion on:

Using Excel's Find and Mid to extract a substring when you don't know the start point

Excel Auto_Open macros: I posted a question on this after some serious digging in my Excel resources. I could find nothing until one of the answers told me naming a macro Auto_Open would run it when the workbook opens.

Once I knew this I was ableto turn up confirmation but mostly as vague references to 123 or errors in MS Technet.

I can't believe how hard it was to discover something so simple and think it would make a good topic. Thanks for all the info you share ... it is improving my results!!!

I tried this example in Excel. It only works if there is a - in every cell. Otherwise Excel returns a #value! error. I also tried using Search. The same problem appeard. Is there a way to get this example to work if the condition is false?

the FIND() function will return -1 (or maybe 0 in Excel since values are indexed starting at 1) if the character does not exist in the cell. So you can quickly test using something of the like of

IF(FIND("-",someCell) > 0, MID((FIND("-",someCell)+1), 2), someCell)

This will return the first two characters after the "-" character if it exists. Otherwise, it will just return the current content of the cell.

That was the first thought that occurred to me too, but it doesn't work. In my case I'm truncating ICD-9 codes. I want V77.1 to return V77 and I want 472 to return 472. It's the codes without a period (what I'm matching on) that are causing the problem. Even with the IF:

=IF(FIND(".",C3)>0,MID(C3, 1, FIND(".",C3)-1),C3)

returns #VALUE!

I found a solution to my problem (and yours):

=IF(FIND(".",CONCATENATE(C3,".")) =LEN(C3),MID(C3, 1, FIND(".",C3)-1),C3)

Where C3 is the field you care about. Basically, if the character you're looking for (in your case "-", in mine, ".") isn't in the string, you get #VALUE! and that cascades through all other Excel functions and screws everything up, right? So you make sure the character is in there. You CONCATENATE(C3, "."), i.e., stick the character at the end. Then if FIND or SEARCH returns LEN(C3) + 1, you know that the character was originally missing from the string. So that becomes the basis for your comparison. It's ugly, but it works.

In the scenario being used in this article, the formula would instead be:

=IF(FIND("-",CONCATENATE(C3,"-")) = LEN(C3), MID(C3, FIND("-", C3) + 1, 2), "")

(Tested in Excel 2003 SP3)

asdas

sad

asd

very helpful for me, can you just give me an idea, if we talk about something like "abcdef - wanthis - ghijk" in a cell, and wants this wantthis in between those two -, How will i do that

I need a formula that will extract the characters between a set parens ie (xxxx) result should be xxxx.

Any tips?

Try this

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

I have a numerical value I need to sort out. They are dates that have come from another database, and they end up in my Excel file looking like 1052008 or 11052008. All the years have four digits, all the days have two digits, but some of the months have one digit and some of the months have two digits. I can get the year out alright with =RIGHT(A1,4), but I'm having trouble getting the month and the day out properly. Any suggestions?

I have to select a word in a string, with help of a formula. The words are on the same place all the time, but they are not equally long. Which formula do I have to use?
Examples of the strings:
- 05/02/2003 09:51:44 127.0.0.1 - WWW "GET /p17/index.htm HTTP/1.1" 200 482 414 482 46 - - -
- 05/02/2003 09:51:44 127.0.0.1 - WWW "GET /p17/hoofdpijn.htm HTTP/1.1" 200 3578 460 3578 0 - - -

The words I have to select are 'index' in the first string, and 'hoofdpijn' in the second.
Can someone help me with it? Thanks!

The above is brilliant, but, if we use the example above, I need to insert a space after the dash, before I extract the data. How can I do this?

what is formula in cace of having "-" 2nos?

When i try to use FIND, I get the error:
Compile Error: Sub or Function not defined.

Many thanks. I was lacking inspiration but your article pointed me in the right direction to undertake my specific challenge.

Problem - I want to generate some web log stats, but not with the hideously long URLs. I start with URLs like this:
/hfm/globalworkspacenav/bpm/modules/com/hyperion/hfm/web/appcontainer/adf.asp

I want to end with short URLs like this:
adf.asp

Where to start? Well, now I know it's ultimately going to be a combination of Mid and Find I start by counting the separators. First eliminate them all:
=SUBSTITUTE(A1,"/","")
this is the remainder (hfmglobalworkspacenavbpmmodulescomhyperionhfmwebappcontaineradf.asp in this example)
=LEN(SUBSTITUTE(A1,"/",""))
(67 in example). Then the whole
=LEN("/"&A1)
(78 in example). Note I've added an extra separator at the start. This is to ensure that the formula does not fail if there are no separators in the first place. So number of separators is (whole - remainder):
=LEN("/"&A1)-LEN(SUBSTITUTE(A1,"/",""))

Now I know how many there are, I can replace the last one with something unique (I can use a ">" because they don't appear in URLs):
=SUBSTITUTE("/"&A1,"/",">",LEN("/"&A1)-LEN(SUBSTITUTE(A1,"/","")))
(//hfm/globalworkspacenav/bpm/modules/com/hyperion/hfm/web/appcontainer>adf.asp in the example)

Now I'm back to the topic of this article - I now want to find the position of the ">":
=FIND(">",SUBSTITUTE("/"&A25,"/",">",LEN("/"&A25)-LEN(SUBSTITUTE(A25,"/",""))))
(71 in the example) However, because I added the extra "/" at the start to ensure that the substitution would always work, there is no need to add 1 to the result to get the start point of the part I want. I don't know the number of characters I want, so choose 100 because it is large enough to ensure there is never any truncation. The final formula:

=MID(A1,FIND(">",SUBSTITUTE("/"&A1,"/",">",LEN("/"&A1)-LEN(SUBSTITUTE(A1,"/","")))),100)

Now if Excel had a find function to search from right to left, it could have been a lot easier.

Thank you for the excellent explanation of how this works.Is there a variant on this formula to solve the problem I have.

This problem is I have a list of bookmakers prices in a column of cells.
e.g.

6/1
9/4
16/1
13/2
11/10 etc....

I can use the above formula to extract the number to the right or left of the / in any given price,however,if I need both numbers to the right or left I have to change the formula.Is there a single formula that will display however many numbers to the right or left of the /.As I load different sheets of info the fraction in the cell is changing,hence my problem.

again,great info
regards, martin martinsemail2000@yahoo.co.uk

Numbers to the Right: =RIGHT(A2,((LEN(A2))-(FIND("/",A2))))
Numbers to the Left: =LEFT(A2,((FIND("/",A2))-1))

Where A2 is your source string (like 123/456)

Please how do i extract after the 3rd dash. In the example above there is only one dash, what if I have 3 dashes i.e 01-06-2001-Monday, here I will like to extract "Monday". Cheers

How to get specific file?
www.Orifile.com is a search engine that is combined with Google, which in orifile.com we can download a specific file for free to format the document, power point, pdf and excel.
And the result is directly formatted doc, ppt, pdf and xls.
There is an additional feature that is fast login where we can access the website facebook, youtube, twitter with 1-click step.
by using the website orifile you save time, many websites that offer sophisticated search but the results obtained are empty. because they only give the results of relevance to the keywords you are looking for.
make orifile as the default domain your search engine.
Features:
? Quick document search on Google.
? Search for Word (.doc), PowerPoint (.ppt), Excel (.xls) and PDF (.pdf) docs online.
? Free and easy to use.
If you?re looking for some kind of document template (i.e. housing contract, NDA agreement) then make sure to read previously published article

How would I go about extracting the company name from the below strings?

Card Purchase SHELL Service St 979301 SHELL xxxxx xxxxx xx
ACH Withdrawal Company Name INC LOAN PAYMT xxxx xxxxxxxx
Recur Purchase with AT NFI*WWW.NETFLIX.COM/CC NETFLIX.COM CA

Hello Jeff et al,
I have encountered a problem in extracting substrings when dealing with data of the following type:
0200239841232000000000000000000
Each of the zeros has meaning. I need to put this string into different columns based on substrings of different lengths (first column will have first 8, second column 1, third column 1 etc).

But when I try to extract the first eight digits, Excel displays it as decimal (2.002398). This problem is not present when there are no trailing zeros in the cell value.

Requesting for kind help in solving this issue.
Prasanna

Is there a way to use "and" or "or" or something to be able search a word that may have be capitalized or not capitalized....or for that matter, multiple different words within a string assuming any instance only contains one of the possible word choices?

Thanks.

The formula is very helpful
But can you add another help by searching for "-" and "/" both together coz my data has both the characters.

Well I am very much thankful to u for describing the find function so easily that people like me who are beginners of excell can also understand it.

Instructions were concise and helpful. Thanks.

Advance Excel & MIS Training Institute

Dear Learner
IPT has provide you Training for Advance Excel 2013, Excel Dashboard , MIS Reporting in Excel and Excel VBA Macros Code Programming ,
Out training conduct on Class Room and Online at your home and our centre , we have class recording facility, after class you will take all class recording , our trainer has a more then 10 years working expr.
Course Bullet
1. Excel All Option and Command
2. Advance Formatting
3. Pivot Data Reporting and Advance Pivot Concept
4. More then 200 Excel formulas
5. Array Formulas
6. Array with VLookup,HLookup and more Formulas
7. Formulas Nesting
8. Other Data Connection
9. Dynamic Chart and Graph
10. Report Automation
11. Dashboard Reporting
12. Excel VBA Editor Concept
13. Coding object Model
14. Data type , LOOP, IF Else
15. Excel VBA Event and Formulas Making
16. User form
17. USerform Connection
18. Outlook Mail Connection
19. CDO Gmail connection
20. WebForm Connection
21. Pivot and Chart VBA Event
22. Other Application and Powerpoint Connection


Call for Free 3 Days Demo Training 8802579388
www.hrca.webs.com