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


Change them back at a later time. By using our site, you acknowledge that you have read and understand our Cookie Policy , Privacy Policy , and our Terms of Service. Not recommended but Accessibility button has option to override web page colors. I double click my series and click on the Y Error Bars to add error bars.

How the color palette works (#palette)


We copied this Find function down the column so you can see that it returns a different value each time, depending on where the dash appears in the strings in column A. In order to extract the first two characters that follow the dash, you'll use the Find function to locate the dash and add 1 to that result. The Find function returns the location of the dash; you want to start extracting characters beginning at the next character, the one following the dash.

That formula says, in English, "Look at A2; determine the position of the dash in A2; add 1 to that value; then return the next two characters. The Mid function relies on the Find function to tell it where the dash appears in the source string. You add 1 circled in red to the Find function's result to start the extraction in the correct place.

Only the beginningWe've just scratched the surface of what you can do by combining the Find and Mid functions. In a future article, we'll show you how to use a variation of this technique to transpose first and last names to format them as Last, First. To comment on this article or to share your favorite Excel tip, please post a comment below or drop us a note.

Inside the Raspberry Pi: How self-driving tractors, AI, and precision agriculture will save us from the impending food crisis. How IoT, robotics, and AI are tackling one of the biggest problems of the century. How digital farming is revolutionizing the future of food. We deliver the top business tech news stories about the companies, the people, and the products revolutionizing the planet.

Our editors highlight the TechRepublic articles, galleries, and videos that you absolutely cannot miss to stay current on the latest IT news, innovations, and tips. The Find function returns the position of the dash in each of the strings in column A. My Profile Log Out. Address Cells 1, columnName 1. Siddharth Rout k 14 I played with your code, change rng to rng , change Set rng Eventually saw the tiny difference: Thank you for taking the time to write up this example, it was certainly helpful.

Thanks for the link as well! Glad it all worked out: Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. Post Your Answer Discard By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service , privacy policy and cookie policy , and that your continued use of the website is subject to these policies.

Adios to Winter Bash