Extracting cells with text from a range of cells

Sweedler

New Member
Joined
Nov 13, 2020
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Hey

So I have a range of cells (40 of them) all containing a formula gathering information from a text string. This range will randomly have certain cells equal to text from the text string and the other cells are just spaces.

Ideally I would love to be able to just extract the text automatically from those strings, but I have not figured out the way. That solution would be more valuable.

The thing is that the amount of spaces varies with every output from the external program that generates my text string.

Basically, what I see is a row of 40 cells, in 8 of them there is text or a number or something .. I need those to be automatically extracted. No VBA in this case.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
You haven't really given us an idea of how this range is structured (is it a single column with 40 rows).
And you haven't shown us what these formulas look like (are the returnings blanks/empty strings, or actual spaces, when not returning data).

If it was a single column of data, and if you using the TRIM function to convert the blank spaces to empty string, you may be able to use Advanced Filters to filter out the blanks and return the data to another range.

If that will not suffice, please provide us with examples and your formulas.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Sweedler

New Member
Joined
Nov 13, 2020
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Thank you all .. I found the answer I found the answer while searching around the internet.

I ran the raw data via a TRIM function and that took away the randomness of the spaces and allowed me to extricate each word.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,290
Members
416,962
Latest member
samfuge

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