» 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.



eg.
Lam Chi Vinh
into
"Lam Chi " and other cell contain "Vinh"