# Finding The Last Space In A String

Nov 17, 2006

there is AT() which finds the first occurrence of a string searching from the left like FIND() and SEARCH(), and RAT() which finds the first occurrence searching from the right-to-left.

A single column with names:
John Doe
Jane Marie Smith
Dr. Alex R. Jones
Bill

I need two columns that would look like:
John.................Doe
Jane Marie........Smith
Dr. Alex R.........Jones
Bill

## Locate Text String After First Space And Before Last Space

May 26, 2007

I have been working on different formulas to return the text string between the first and last space and have been unsuccessful. Is this possible?

I have tried several combos or Left and Right, I have been able to get the values after the first space, and the values before the last space, but not between the spaces.

String: Y60
~C CULT NUCLEUS 3X2 SPRING WST BK XL

Desired results: D60
CULT NUCLEUS 3X2 SPRING WST BK

## Finding Characters Before A 'space'

Jan 23, 2007

I am trying to find numbers before a space character.

For example...

In column 1..................to return in column 2

23 cars................................................. 23
5 trucks................................................. 5
150 vans.............................................. 150

I can't use MID Function as it can be 4 numers long...

## Finding Right Syntax For Concatenating A Space In If / Then Formula

Apr 16, 2012

Below is the "real formula"

=IF(SUM(\$F\$4-\$E\$4)= 0," ",SUM(\$F\$4-\$E\$4)) 'If the value is 0 then enter a space in the cell.

And below is the VBA formula

"=if(sum(" & LastRowCell & "-" & FirstRowCell & ")= 0," & " " & ",sum(" & LastRowCell & "-" & FirstRowCell & "))"

The real formula works, but I am having having difficulties with the syntax of the VBA formula. The above VBA formula works to, but enters a 0 in the cell instead of a space.

## Split A String Where The Space Is?

Dec 19, 2008

I'm trying to split the string in textbox 1 so i get the first name and the last name but this doesn't seem to work?

## Trim String At First Space?

Mar 11, 2014

I have some code that grabs the name of a staff member. The format is:

Code:
Result = Trim(Sess0.Screen.GetString(8, 33, 10))
.Cells(r, 15).Value = Result

How do you trim the string before the first space so the value in Result would be just the last name (DOE)?

## Automatically Insert Space In A String

Dec 18, 2008

I have a column called "Unit Size," which contains data such as 200 g, 0.32 g, 15 mL, 2 mg.

I need a macro to automatically insert a space between the numbers and the letters, for example, if someone types 10g it automatically converts it to 10 g.

The strings may be of different lengths, i.e. contain different numbers of decimal places or signficant figures.

## Macro To Separate String If Space Between

Jan 29, 2009

On sheet one, in the column column C I have cells which have characters separated by a space character (For example cell C3 looks like this: "L C"; cell C12 looks like this: "S BF54FR"). There are only a few cells which have more than three characters. The characters are separated by a space character. I would like the macro to do the following: take out each on of the character in put in the same row next column. (For example if in column C has the value: "L C" then in the same row column D should have the value "L" and column E should have the value "C". Or if column C was "S BF54FR" then column D should have the value "S" and column E "BF54FR".

## Extract String After First Space And Numbers?

Jun 30, 2014

I'm looking for a way to extract the data after the first space in a string to after the first set of numbers (always 8 digits). The formula I'm using is not accurate since the number of words that appears between the first space to the first set of numbers changes.

Sheet2ABC1Desired results2277 xxx spzrtmhk
companies 12345678 231516990 2475.06 3199.57 23.19 street 1 739.00xxx spzrtmhk
companies12345678 231516990xxx spzrtmhk companies 1234567831528 dts powertrain comp inc
15981808 231516989 43.55 84.40 48.40 5.00dts

[Code] ...........

## Adding Space To A String Of Characters

Feb 6, 2007

I need to add spaces to a string of characters so that it is readable, the database is huge so I cannot do it manually:

For example,
123456John Doe 3456Adam Sharp I need it to apperar as:
123456 John Doe 3456 Adam Sharp

but still remains at the same column. Is there a quick way to do this ?

## Delete String After Last Space In A Cell

May 4, 2007

I am interested in some sort of macro or formula that will strip all of the characters in a cell that come after that last space. My difficulty lies in that the string isn't a constant character count nor is the string always the same.

American Legion Post 8754 Hartford
YWCA Canton

I would like for it to say:
American Legion Post 8754
YWCA

## Code To Space String Of Text

Jun 9, 2008

Need code that will indent text in a cell 5 spaces over.

## Extract Text After Space In String

Oct 5, 2006

i have used the RIGHT function to remove the 1L/1S and the 1L from the samples belows

the problem i have is when the 1L is in its cell there are 3 spaces before it
how can i remove those spaces so that all the data is to the left of the cell

BYA001 1L/1S
BYA003 1L

## Covert Space Delimited String To Array

Apr 24, 2008

I have two columns of data, one for Position, and one for description.

The positions are concatenated and separated by spaces. E.g. C1 C2 C3 for each Description. I need to create a separate row for each position

So I need to convert:

Pos Decscription
C1 C2 C3 Cap 100n 10% X7R 0805

to:

Pos Decscription
C1 Cap 100n 10% X7R 0805
C2 Cap 100n 10% X7R 0805
C3 Cap 100n 10% X7R 0805

Or in other words split a space delimited string into an array, so that I can loop through the array, and add new rows

## Formula To Find Characters After Last Space In A String

Aug 27, 2007

I have text string and would like two formulas to find,

1) the characters after the last space and

2)to find the number of characters after the last space.

## Replace Space With Dash In Text String?

Aug 7, 2013

I am looking to replace the space in between numbers and letters in a text string. The number of entries varies with each row.

Example of the entries in the column I need to reformat. Each entry is in a new row.

Code:
10 SSS
24 MNL
17 HLG 18 LTN
17 CBG 17 HLG 15 HIL 15 thn 22 ALV
17 ELP 34 HLG 15 HIL18 THN 10 TTL

What I am looking to achieve:

Code:
10-SSS
24-MNL
17-HLG 18-LTN
17-CBG 17-HLG 15-HIL 15-thn 22-ALV

17-ELP 34-HLG 15-HIL 18-THN 10-TTL

## Formula To Find Characters After The Last Space In A String

Aug 28, 2007

I have text string and would like two formulas to find, 1) the characters after the last space and 2)to find the number of characters after the last space.

## Separate Space\$ Delimited String Into Multiple Cells

Oct 13, 2009

I am trying to split a string into separate cells. I have managed to generate the formula for the description and first dlr value in the string but I am have trouble figuring out how to build functions for the rest of the string.

Example attached.

## Finding The Frequency Of A String In Other String(s)

Dec 8, 2009

I'm looking for a formula that will count specific word in a cell.

Let say cell a1 has "WLLWWLLLLLLWWLLLWWWLLLWWLLWWLWLWLWLLLWWW"
I want to know how many WWLL are in this cell, which is 4.

I wrote this custom function to provide the answer. It is used like so: ..

## Finding One String Within Another

Mar 13, 2013

I have this formula on my worksheet to test if column A="-" or "+"

Code:
=IF(OR(A:A="+",A:A="-"),NA(),"")

I would like to enhance this so that the test looks for the the + or - inside column A which might be a whole string rather than just one character and returns the same result (an NA# error if found).

I am doing this so I can then use SpecialCells to clear the cells containing the + or -

## Finding Text Within A String

Oct 24, 2008

On my spreadsheet I have a column entitled: "Item Title", and the data within this column can look like the following;

## Finding String In Array

Aug 8, 2013

I have list of strings to be searched in column A2 to A150 (A1 has column title). The array that needs to be searched is in B2 to AG1000 (B1 to AG1 has column title). I want each string in column A to be searched in the full array. If match is found the corresponding column title (B1 or C1 and so.) and cell address needs to written to a new result columns in AY and AZ. it should work for duplicates as well i.e. the string can be in all the columns of array from B to AG. and all of them needs to be written to result column.

## Finding The First Number In A String?

Jul 31, 2014

I'm trying to extract the number portion from the following string. I'm trying to use FIND to find the first digit between 1 and 2 so I used an "or" argument along with the code number, however it's not working here. What I really want to do is find any digit between 1 and 9 in the string, however I started with this formula for now. why this doesn't work and provide a better formula. I want to understand why this didn't work to understand the functions better. Text string is as follows "standard VAT rate: 20% (Jan 1984)". The formula I used in the following =MID(A253,FIND(OR(Char(49),CHAR(50)),A253),2) where A253 contains the string. Is it possible to use a logical argument within a find?

## Finding Numbers From String

Aug 11, 2009

I am trying to find numbers from a string. I have for example words "EUR Fwd 9x12" and "Eur Fwd 11x15" And i want to write a function that reads the first number from a string if there is only one number before "x" or two numbers if there are 2 numbers. So I have tried to build following function:

Function NumbersInString(Word As String) As Integer
Dim i As Integer
Dim FirstNumberInString As Integer, SecondNumberInString As Integer
For i = 1 To Len(Word)
If IsNumeric(Mid(Word, i, 1)) Then
FirstNumberInString = Mid(Word, i, 1)
If IsNumeric(Mid(Word, i + 1, 1)) = False Then
Exit Function
Else
SecondNumberInString = Mid(Word, i + 1, 1)
End If
End If

Next
NumbersInString = FirstNumberInString & SecondNumberInString
End Function

## Finding The First Number In A String

Apr 8, 2007

We are trying to make either a macro or a function that will look through the contents of a cell, find the first NUMBER and then paste the results to another column. Below is an example of what may be in cell A1:

#BC7K,03/30/2007,0.00636,0.0069,0.00614,0.0062,0.0,0

We want only to find the FIRST NUMBER in this string, so the result should be 7.

## Lookup But First Finding A Unique String?

Jul 3, 2014

mockup.xlsxStarting out with a 9 by 11 grid.

Column A will have times that need to be referenced in a lookup. (Ex Sat 8:00am)

Row 1 will have basball field codes reference with a lookup. (Example Field 4

Cells B2:I11 have distinct codes or possibly nothing. (An example would be: Tier 1 A1-A3)

These codes will move around from time to time. They will always remain distinct within the grid, but could appear in any of the cells from B2:I11.

I have 2 columns I want to populate with values from the grid.

First is Fields. I want the first cell to populate with the baseball field found in row 1 of which the code Tier1 A1-A3 falls under.

For example if this season Tier1 A1-A3 is found in the column under Field 4, then I need to return Field 4 as a result.

Second is the time. I want the time to be populated based on what row the Tier1 A1-A3 code falls in.

For example if Tier1 A1-A3 is in the Sat 8:00am row, then I need that value returned.

In short, I need to search the grid and find a given value. I then need to use a lookup (or other tool) to return the first value in that row. And secondly via lookup return the first value in that column.

edit: added spreadsheet example. First sheet is grid in question, 2nd sheet is a typical schedule.

The first Team (Scared Hitless) is A1, the team they play first is Sands Duel Fuel (A3) They play at 11:00am on Field TR3.

You will see on the grid under TR3 and Sat 11:00 the code Tier 4 A1v3. This code is what I want to search and correlate into times on the schedule rather than doing the data entry by hand.

## Find Is Not Finding Character String?

Jun 11, 2014

I have a cell that has approx 22000 characters. I'm trying to remove a specific character string from a cell by doing a find and replace with "". It works for characters in the first part of the cell but not for characters in the last part of the cell.

Example: I do a find/replace for the characters 21242 to "" in column A and I get the expected results. I do a find/replace on 69294 to "", again I get the expected results, but if I do a find/replace on 85203 to "", I get "Excel cannot find what I am searching for" (but its there!)

My cell size is within the max size of 32,767 characters so not sure why its not working.

Attached a sample worksheet.

## Finding A Specific Integer In A String

Aug 3, 2009

I have a string in one of the following formats:

Banana 2, Orange 5, Lemon 0
Banana 7, orange/Lemon 9, cucumber 6
Melon/grape 3, Pineapple/ Orange 1
Banana 1, orangefruit/Lemon 2, pine 8

I would like to take out the first integer that comes after the word Orange (not case sensitive). I'm kinda at a loss here, how do I go about accomplishing this?

## Finding Numbers In Text String

May 11, 2010

In a data report i have a column which contains a mix of text and may contain an 8 digit number which could start in 0.

for example

in column A i could have "Hello how are you 01237232 I am fine"

I wish to extract the number into a seperate column, and would ordinarilly use a mid or left/ right function, however the text infront and behind the number will vary in length, which means i cannot do this.

The number will always be 8 digits, could start in 0 but will not always, and it may not be present in all the cells in this column.

How I an achieve this?

## Finding A String In A Column, Displaying YES On The Same Row

Feb 16, 2007

I am trying to search for a string of numbers (column 2) in an array, and have "YES" be written on the same line in column 3 if the string is found in the names ANYWHERE in column 1. Please see the desired results on the picture in column 3.

I have tried many things, including SEARCH function which can only work with 1 cell not many, COUNTIF and more advanced functions, but I think have not succeeded because of my lack of knowledge in arrays.