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?

 

[Trebor76]

Hi bmarion91,

Welcome to MrExcel!!

Here's one way (just change the cell reference to suit):

=MID(A2,SEARCH(" ",A2)+1,LEN(A2)-SEARCH(" ",A2))

Regards,

Robert

 

[Rick Rothstein]

Here's one way (just change the cell reference to suit):

=MID(A2,SEARCH(" ",A2)+1,LEN(A2)-SEARCH(" ",A2))

I am wondering if a straight formula is the way to go or not. The OP said some of the cells have all the text in one cell meaning some don't. For the ones that don't, that must mean they are already separated. so you cannot use a formula copied down... rather, I think the formula must be placed in the blank cells of Column B which means they need to have their references adjust accordingly.

 

[Trebor76]

that must mean they are already separated

Hi Rick,

I didn't read it that way actually. I thought that the cell either a single word or multiple words. I could be wrong of course!!

If my reasoning is correct the following is preferred as my original formula will return #VALUE! if the cell doesn't contain a space:

=IFERROR(MID(A2,SEARCH(" ",A2)+1,LEN(A2)-SEARCH(" ",A2)),"")

Thanks,

Robert

 

[Rick Rothstein]

I didn't read it that way actually. I thought that the cell either a single word or multiple words. I could be wrong of course!!

Okay, I see how you are reading it. You might be right, but a list of acronyms by themselves mixed with acronyms and their meaning seemed strange to me, so I assumed the cells with only an acronym must have meant the meaning already existed in the next cell. I could be wrong of course!!

 

[ClaireS]

Cheesy, I Know, but try this if you are running Excel 2013 or later:

Make sure that the first entry is an acronym with a definition - make up one if you have to.
Type the acronym only in a column to the right of the first entry.
Type (or start to type) the second acronym - Flash Fill may do the rest!
You can then do the same with the "after acronym" but in a second column

Flash Fill is always worth a shot - it may not be able to work out what you are trying to do, but if it does work, it's brilliant!

 

[bmarion91]

Okay, I see how you are reading it. You might be right, but a list of acronyms by themselves mixed with acronyms and their meaning seemed strange to me, so I assumed the cells with only an acronym must have meant the meaning already existed in the next cell. I could be wrong of course!!

Rick,

You guys pretty much have the general idea.


Some data is presented like so:

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.

Thanks for helping me out with this guys. I've tried to find where I need the formulas to go but i'm not quite there yet. I'll keep trying

 

[Rick Rothstein]

Given the mix of correct cells and cells that need processing plus the need to move some of what is in Column A to Column B, you cannot do what you want with formulas only (you would need some helper cells plus individual copy/pastes and partial deletes). I believe a macro solution would be more practical. Here is such a macro...

[table="width: 500"]
[tr]
	[td]Sub Acronyms()
  Dim Cell As Range, Parts() As String
  For Each Cell In Range("B1", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlBlanks).Offset(, -1)
    Parts = Split(Cell.Value, " ", 2)
    Cell.Offset(, 1).Value = Parts(1)
    Cell.Value = Parts(0)
  Next
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Acronyms) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[alansidman]

I might be inclined to use Power Query and the following MCode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}})
in
    #"Changed Type1"

 

[sandy666]

@alansidman
IMHO, you can remove both #"Change Type" and stay with single line.