Is it possible to fo on excel?

S

skuba

Hi,
I need to copy the first world of a column into another colums.
E.g.
A1 is Brande Blue Widget

I wanted to be able to run a kind of find and replace that would loo
for the first word (or everything before the space) on column A1 an
place that first word on A2.

Is there any way to do that?

Even better would be to get the first word that starts wit
alphabetical characters. Not numbers.

Thank
 
R

Robin Hammond

Oops. Try not to press that send key by mistake!

The first part is easy with a standard formula

=LEFT(A1,FIND(" ",A1,1)-1)

To get the left most word that is not numeric, there is probably a way, but
this is a quick UDF that you could use. Open the VBE, Insert a module, and
paste this in, then use it as a formula = leftalphaword(a1)

Public Function LeftAlphaWord(rngCell As Range) As String
Dim strInput As String
Dim nStart As Integer
Dim nEnd As Integer

If IsEmpty(rngCell.Value) Or IsNumeric(rngCell.Value) Or _
IsError(rngCell.Value) Then Exit Function

strInput = CStr(rngCell.Value)

nStart = 1
Do While IsNumeric(Mid(strInput, nStart, 1)) Or Mid(strInput, nStart, 1) = "
" _
Or nStart = Len(strInput)
nStart = nStart + 1
Loop
If nStart = Len(strInput) Then Exit Function

nEnd = nStart
Do Until nEnd = Len(strInput) Or Mid(strInput, nEnd, 1) = " "
nEnd = nEnd + 1
Loop
LeftAlphaWord = Mid(strInput, nStart, nEnd - nStart)

End Function

Robin Hammond
www.enhanceddatasystems.com
 
M

Max

Maybe something to get you going
on the first part of your query?

With say "Brande Blue Widget" in A1

Try in B1: =LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-1)

B1 returns the first word "Brande"

Copy B1 down as many rows as there is data in col A
 
M

Max

whoops, you did say you wanted the first word
to be put in A2?

The lines should read:

With say "Brande Blue Widget" in A1

Try in A2: =LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-1)

A2 will return the first word "Brande"
 
M

Max

skuba > said:
.. Even better would be to get the first word that
starts with alphabetical characters. Not numbers.
Click to expand...

Here's a formula to try with respect to
the 2nd part of your query above

Put in A2

: =IF(ISNUMBER(VALUE((LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-
1)))),MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,SEARCH(" ",TRIM
(A1),SEARCH(" ",TRIM(A1))+1)-SEARCH(" ",TRIM(A1))-1),LEFT
(TRIM(A1),SEARCH(" ",TRIM(A1))-1))

With say "Brande Blue Widget" in A1, as before
A2 will return the first word "Brande"

With say "123 Brande Blue Widget" in A1,
A2 will now *also* return the first word "Brande"
(after the numbers)
 
S

skuba

Sorry for keep asking. There is one more thing. The only time I need t
skip the number is if it has 4 numbers. It will always be an year
2000, 2001, 2002, 2003 or 2004, etc...

So, let's say cell is 2004 Brand Widget
I want to have on the other cell just Brand

Is it possible?

Hey Max, this
=IF(ISNUMBER(VALUE((LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-
1)))),MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,SEARCH(" ",TRIM
(A1),SEARCH(" ",TRIM(A1))+1)-SEARCH(" ",TRIM(A1))-1),LEFT
(TRIM(A1),SEARCH(" ",TRIM(A1))-1))
Click to expand...
is not working. Excel doesn't recognise as a formula.

Thanks a lot for helping,

Ya
 
M

Max

skuba > said:
is not working. Excel doesn't recognise as a formula ..
Click to expand...

You have to remove the line breaks caused by the wrap
when you copy>paste the formula into the formula bar
 
S

skuba

Thanks Max, but this won't work.

I got pretty close to what I need
=IF(ISNUMBER(--LEFT(D1350,4)),MID(D1350,6,SEARCH(CHAR(127),SUBSTITUTE(D1350,"
",CHAR(127),2))-SEARCH(" ",D1350)),LEFT(D1350,SEARCH(" ",D1350)))
Click to expand...

I just have one problem here.
It will also cut out words with 3 numbers.

I just want to cut out words with 4 numbers, like years 2003, 2004,
etc.....

Any way to fix it?

Thanks
 
M

Max

I just want to cut out words with 4 numbers, like years
2003, 2004, etc.....
Click to expand...

ok, going by your revised specs above, I'll interpret it
to mean that you want to extract the first word only if
the text-string in cell D1 contains a 4 digit number, and
return a blank [""] if otherwise ..

The revised "ugly" formula below seems to work for me,
when put in say D2:

=IF(ISERROR(AND(VALUE((LEFT(TRIM(D1),SEARCH(" ",TRIM(D1))-
1)))>=1000,VALUE((LEFT(TRIM(D1),SEARCH(" ",TRIM(D1))-1)))
<=9999)),"",IF(AND(VALUE((LEFT(TRIM(D1),SEARCH(" ",TRIM
(D1))-1)))>=1000,VALUE((LEFT(TRIM(D1),SEARCH(" ",TRIM(D1))-
1)))<=9999),MID(TRIM(D1),SEARCH(" ",TRIM(D1))+1,SEARCH
(" ",TRIM(D1),SEARCH(" ",TRIM(D1))+1)-SEARCH(" ",TRIM(D1))-
1),""))

--

Maybe a slightly "shortened" version of the above,
put in say D3:

=IF(ISERROR(_4D),"",IF(_4D,MID(TRIM(D1),SEARCH(" ",TRIM
(D1))+1,SEARCH(" ",TRIM(D1),SEARCH(" ",TRIM(D1))+1)-SEARCH
(" ",TRIM(D1))-1),""))

where _4D is a named* range referring to:

=AND(VALUE((LEFT(TRIM(Sheet1!$D$1),SEARCH(" ",TRIM(Sheet1!
$D$1))-1)))>=1000,VALUE((LEFT(TRIM(Sheet1!$D$1),SEARCH
(" ",TRIM(Sheet1!$D$1))-1)))<=9999)

*named range created via: Insert > Name > Define
--

The above formulas will return blank [""]
if D1 contains, for example:

Brande Blue Widget
2 Brande Blue Widget
20 Brande Blue Widget
200 Brande Blue Widget

The above formulas will return the 1st word "Brande"
if D1 contains, for example:

1999 Brande Blue Widget
2000 Brande Blue Widget
2001 Brande Blue Widget
2002 Brande Blue Widget
etc

Use of TRIM() in the formula helps to remove any
extraneous "invisible" leading or trailing spaces
in D1's contents which might otherwise produce
errors in the desired results

Hope this gives you what you're after ..
 
M

Max

skuba > said:
Robin, I got a syntax erroe from your formula...
Click to expand...

It's the same line break problem (due to wrap)
when you copy-pasted Robin's UDF as-is into the VBE
(you'll see the "red" highlights)

The line which got hit is the long 2nd line just after
"nStart = 1" in:

nStart = 1
Do While IsNumeric(Mid(strInput, nStart, 1)) Or Mid
(strInput, nStart, 1) = " " _
Or nStart = Len(strInput)
nStart = nStart + 1

The last part of the 2nd line should be like this:
(after you remove the line-break in the VBE)

... Or Mid(strInput, nStart, 1) = " " _

There should be a space between the double quotes: " "
followed by a space, then an underscore

After you correct the above, you won't hit the error
 
Top