Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get relative row numbers in a range in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Left Lookup in Excel
  • Find closest match in Excel
  • Extract all partial matches in Excel

Data Analysis Examples

  • How to count table rows in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Error Bars in Excel
  • How To Create Frequency Distribution in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation must begin with
  • Excel Data validation require unique number
  • Excel Data validation allow uppercase only
  • Excel Data validation whole percentage only

How to split text string at specific character in Excel

by

To split a text string at a certain character, you can use a combination of the LEFT, RIGHT, LEN, and FIND functions.

Formula

=LEFT(text,FIND(character,text)-1)

Explanation

In the example shown, the formula in C5 is:

=LEFT(B5,FIND("_",B5)-1)

And the formula in D5 is:

=RIGHT(B5,LEN(B5)-FIND("_",B5))

How these formulas work

The first formula uses the FIND function to locate the underscore(_) in the text, then we subtract 1 to move back to the “character before the special character”.

FIND("_",B5)-1

In this example , FIND returns 7, so we end up with 6.

Worked Example:   How to remove trailing slash from url in Excel

This result is fed into the LEFT function like as “num_chars” – the number of characters to extract from B5, starting from the left:

=LEFT(B5,6)

The result is the string “011016”.

Worked Example:   Get last name from name with comma -- Manipulating NAMES in Excel

To get the second part of the text, we use FIND with the right function.

We again use FIND to locate the underscore (7), then subtract this result from the total length of the text in B5 (22), calculated with the LEN function:

LEN(B5)-FIND("_",B5)

This gives us 15 (22-7), which is fed into the RIGHT function as “num_chars” –  – the number of characters to extract from B5, starting from the right:

=RIGHT(B5,15)
Worked Example:   Split dimensions into two parts in Excel Worksheet

Post navigation

Previous Post:

How to split text with delimiter in Excel

Next Post:

How to calculate project complete percentage in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • LEFT, LEFTB functions: Description, Usage, Syntax, Examples and Explanation
  • SEARCH, SEARCHB functions: Description, Usage, Syntax, Examples and Explanation
  • How to extract name from email address in Excel
  • How to display conditional message with REPT function in Excel
  • How to check if cell contains one of many things in Excel
  • Get project end date in Excel
  • Get project midpoint in Excel
  • How to calculate project start date based on end date in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Get day from date in Excel
  • Tax rate calculation with fixed base in Excel
  • How to calculate present value of annuity in excel
  • TBILLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYS function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles