Segregate the first few characters

electrofunk

Board Regular
Joined
May 3, 2005
Messages
70
Hi There

I need to segregate the first 9 characters of address data that is in a cell.

Currently looks like this:
10487 Main St

I need it to look like this:
10487 Mai

(don't ask why :P )

Can someone please provide the formula?
Much appreciated!
John
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

bobinnc

Active Member
Joined
Nov 4, 2006
Messages
281
If the full string is in cell A1 and you want the first 9 characters in cell B1 then put the following equation in cell B1:
Code:
= LEFT(A1,9)
 

blazonQC

Board Regular
Joined
Jun 6, 2007
Messages
92
Say you want the first 9 digits to appear in cell A1 and your text, in this instance an address, is in B1....

Click on A1 and type in this formula:

Code:
=LEFT(B1,9)

HTH,
Chad
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Another alternative is "Text to Column" from the "data" menu, if you don't want to keep the original cell intact.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,397
Office Version
  1. 365
Platform
  1. Windows
Another alternative is "Text to Column" from the "data" menu, if you don't want to keep the original cell intact.
If you do want the original data intact, you can just change the "Destination" address in step 3 of the Text to Columns (Fixed Width) wizard.
 

Forum statistics

Threads
1,181,649
Messages
5,931,217
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top