Extract all Text after Last Blank Space in a cell String
Extract Text after Last Blank Space in a String.
Cell D12 formula: Cell D12 formula Extracts all Text after last blank space in cell A12 string.
———————————————————————————————–
Cell D12 formula: Extracts all Text after last blank space in cell A12 string.
Enter formula in cell D12 and copy down to Extract Text after last space (or last period or dot, or any last character per cell B12), of cell A12 string.
Cell D12 formula:
=TRIM(RIGHT( SUBSTITUTE(A12, B12, REPT(” “, LEN(A12))), LEN(A12)))
Explanation – Cell D12 formula (Non-Array Formula):
1) SUBSTITUTE(A12, B12, REPT(” “, LEN(A12))): Using the REPT function – REPT(” “, LEN(A12)) – repeats a blank space as many times as equivalent to the cell A2 string length ie. it returns 13 blank spaces (cell A12 string has 13 characters). Using the SUBSTITUTE function replaces each occurrence of cell B12 separator (which is a blank space in this instance) in cell A12 string with blank spaces equivalent to the number of characters of cell A12 string (ie. cell A12 length of 13). The new string length will be equivalent to 37 characters (3+ 13+5+13+ 3).
2) RIGHT( SUBSTITUTE(A12, B12, REPT(” “, LEN(A12))), LEN(A12)): using the RIGHT function extracts the characters from the right of the string returned in 1) above equivalent to the number of cell A12 string characters (ie. 13). It means that the last 13 characters from the above string are extracted, which will in every case include the sub-string after last space of cell A12 string. Using TRIM will remove all leading and trailing blank spaces and return the sub-string after last space.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
Cell D3 formula: Cell D3 returns the Position of Nth (C3) occurrence of a character (B3) in a string (A3). If cell B3 character is a letter then there are options of case-sensitive or case-sensitive search. Normal Non-Array formula.
Cell D8 formula: Extract characters after first Space from cell A8 string. Normal Non-Array formula.
Cell D16 formula: Cell D16 formula returns the Count of Blank Spaces in cell A16 String. Normal Non-Array formula.
Cell D19 formula: Cell D19 formula returns the Count of occurrences of cell B19 sub-string (having 1 or more characters) in cell A19 String. Normal Non-Array formula.
Cell D23 formula: Cell D23 formula returns the Position of Last occurrence of cell B23 character(s) in cell A23 string. Cell B23 can contain a single or multiple character. Normal Non-Array formula.
Cell D24 formula: Cell D24 formula returns the Position of Last occurrence of cell B24 character in cell A24 string. Cell B24 can contain only a single character. Normal Non-Array formula.
Cell D27 formula: Cell D27 formula Extracts the Sub-String Preceding Last Occurrence of cell B27 character(s) in cell A27 string – cell B27 can contain a single or multiple characters. Normal Non-Array formula.
Cell D30 formula: Cell D30 formula Extracts the Sub-String Preceding Last Occurrence of cell B30 character(s) in cell A30 string – only single character option in cell B30. Normal Non-Array formula.
Cell D34 formula: Cell D34 formula Extracts the Sub-String after 1st Occurrence of Space or Comma, whichever is later, from cell A34 String. Array formula (Ctrl+Shift+Enter).