Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Copy Partial Data from Cell (omit 2 digits) and Convert to Upper/Lowercase

Posted on 2016-09-06
Medium Priority
55 Views
Data Sample

This is in ONE CELL:    146301 ASPHALT WORKS: OP BY CONTR-PERM & DRVR

I only want the first 4 digits of the number above then I want the ALL CAPS to be Upper/Lowercase Formatting.

Result Desired in ONE CELL:  1463 Asphalt Works: Op by Contr-Perm & Drvr  (or something close,
0
Question by:Julie Lyman
• 2
• 2

LVL 35

Expert Comment

ID: 41787111
This seems to work:
``````=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+3)&PROPER(MID(A1,MAX(FIND({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1,1))+10,99999))
``````

My result:
``````ONE CELL:    1463 Asphalt Works: Op By Contr-Perm & Drvr
``````

Swap all the occurrences of A1 in the formula for the cell you're wanting to target.
0

LVL 48

Accepted Solution

Wayne Taylor (webtubbs) earned 2000 total points (awarded by participants)
ID: 41787114
Try this formula...

=LEFT(A1, 4) & PROPER(MID(A1, FIND(" ", A1), 999))
0

LVL 35

Expert Comment

ID: 41787134
Haha I must be half asleep in my lunch hour... I included ONE CELL as part of the data... oops
0

LVL 48

Expert Comment

ID: 41814569
Only functioning response.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month12 days, 3 hours left to enroll