» Add space between characters and numbers in Microsoft Excel
CATEGORY - Excel Editing , Excel Formula
VERSION - All Microsoft Excel Versions
data in cell A1: A100 Formula in cell B1: =LEFT(A1,1)&" "&RIGHT(A1,LEN(A1)-1) Result: A 100
Book Store:
Recommended Books:
- Special Edition Using Microsoft Outlook 2002
- Dictionary of Finance and Investment Terms
- VBA for Modelers: Developing Decision Support Systems Using Microsoft« Excel
- Positioning: The Battle for Your Mind
- Absolute Beginner's Guide to Microsoft Excel 2002
- H&R Block's Just Plain Smart(tm) Tax Planning Advisor: A year-round approach to lowering your taxes this year, next year and beyond
To sort First, Middle & Last name
Raghunathan Posted on: 31-12-1969
Hi Vincent lin,
paste "=LEFT(C10,FIND("*",SUBSTITUTE(C10," ","*",LEN(C10)-LEN(SUBSTITUTE(C10," ",""))))-1) " for displaying first name & middle name in one cell & paste "=RIGHT(C10,LEN(C10)-FIND("*",SUBSTITUTE(C10," ","*",LEN(C10)-LEN(SUBSTITUTE(C10," ","")))))" in another cell for displaying the last name.
you can also check out http://www.exceltip.com/st/Separating_first_name_and_last_name_without_using_formulas_in_Microsoft_Excel/59.html for changing names without using formula or http://www.exceltip.com/st/Extract_the_first,_middle_and_last_name_in_Microsoft_Excel/370.html for greater details.
regards
Raghunathan
raghu_te@hotmail.com
Post Codes
Gareth Posted on: 31-12-1969
I am looking to do something similar. I am using MS Map to create some "pretty" reports based on the post code for our customers. Here is the query:
CF46 5PL
M11 2NE
B14 7SG
RH13 4RT
The above is a sample of possible post codes within my records. I want to extract the first letter(s) from the post code i.e. RH13 4RT = RH, B14 7SG = B
I want all the letters up until the first number appears (post code area).
Any suggestions would be appreciated, or an alternative within MS MAP that I may not be aware of.
Thanks.
Reply: Post Codes - Gareth from S Wales, UK wrote on August 19, 2003 7:00 AM EST
Alan Posted on: 31-12-1969
Hi Gareth,
If the post code is in cell A1, try this:
{=MID(A1,1,MATCH(FALSE,ISERR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)-1)}
Note that this is an array formula, and must be entered using Shift-Ctrl-Enter.
Does that work for you?
Alan.
Post Codes - Yeah!
Gareth Posted on: 31-12-1969
Alan, thats fantastic !
Appreciate that. All I need to do now is reverse engineer and figure out how the hell it works :)
Gareth
PS I don't suppose you know any useful resources for MS MAP & Excel do you?
Reply - Reverse Engineering
Alan Posted on: 31-12-1969
Hi Gareth,
No problem!
To reverse engineer, you can just work your way in through the brackets.
You need to understand arrays first though (if you don't already).
Sorry - no idea on MS Map. I never used it.
Alan.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
eg.
Lam Chi Vinh
into
"Lam Chi " and other cell contain "Vinh"