• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4553
  • Last Modified:

Need a Excel formula to remove numbers at end of lines of text that are of varying lengths & # of digits

I was able to figure out the TRIM formula to extract the numbers at the end of each line of the SAMPLE TEXT (below) into column C:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))

Next I would like to extract just the text strings, without the numbers (or space in front of the numbers), into column B from the SAMPLE TEXT below. I've taken a few stabs at it but have only managed to extract the first word in each line and not the entire text string.

SAMPLE TEXT:
Documents (in Book) Total 239
Frame generatable file(Skipped, Unprocessed) 0
Unable to generate due to Errors 0
Generated OK 239
Generated containing Warnings or Errors 0
Text Amendments (Insertions/Deletions) Total 40
Activated 40
Warnings or Errors 0
DataLinks (Links to Database) Total 3730
New 23
Changed 45
Warnings or Errors 0

Thanks,
Andrea
0
Andreamary
Asked:
Andreamary
1 Solution
 
helpfinderIT ConsultantCommented:
use this formula (I assume your data ar in A column)
note that based on your regional settings you may need to replace semicolon (;) sign in formula by comma (,)
=LEFT(A1;LEN(A1)-LEN(MID(A1;FIND(CHAR(22);SUBSTITUTE(A1;" ";CHAR(22);LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))+1;255))-1)

see also attached sample
sample.xlsx
0
 
AndreamaryAuthor Commented:
Thanks for your quick response, helpfinder! I converted the semi-colons to commas and the formula worked perfectly.

Cheers,
Andrea
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now