move all text after first word to next cell if applicable

[bmarion91]

I have an acronym list with 2800 rows populated but some of them have both the acronym and the expansion in the first cell.

Is there a formula I can use that will let me move all text after the first space into the cell directly to the right?

 

[jtakw]

CIA Central Intelligence Agency
FBI Federal Bureau of Investigation
NSA National Security Agency

This is the data that I want separated into two separate columns, whereas some data was placed in the worksheet correctly from the start.

I've tried to find where I need the formulas to go but i'm not quite there yet. I'll keep trying

Hi,

In my sample below, where A1:B1, A3:B3, and A7:B7 was Correctly populated in the worksheet from the start...

1. Place formula in B2, enter.
2. Select B2, Right Click, "Copy"
3. Hit F5, Click "Special" ( lower left corner )
4. Select "Blanks" on left side, Click "OK"
5. Click "Ctrl" and "V"

The formula is now copied to all Blank B cells which corresponding A cells are populated, and Cell references auto adjusted.

Book1
	A	B
1	CIA	Central Intelligence Agency
2	FBI Federal Bureau of Investigation	Federal Bureau of Investigation
3	NSA	National Security Agency
4	ATF Bureau of Alcohol, Tobacco, Firearms and Explosives
5	BBB Better Business Bureau
6	DEA Drug Emforcement Administration
7	IRS	Internal Revenue Service
8	DOJ Department of Justice
Sheet447

Cell Formulas
Range	Formula
B2	=MID(A2,FIND(" ",A2)+1,99)