Excel Question (1 Viewer)

[MikeIke]

I'm looking for a way to split out ID numbers from a string that contains both a name and an ID number. For example, I might have cells that read

Ringo Starr A123456

Stevie Ray Vaughn B987321

Bono C223344

I want to split those into two columns - Full name, and ID. I cant use the Text to Columns function because the spacing is inconsistent, and some people have one name, others have two or more.

One direction I was heading is if there is a function that can find the position of the first number, then I could split the data one space over from that. But since the numbers will be different I can't use the Find function. Is there a way to search generically for a numberic value in a text string?

Any other ideas?

 

[iwabaf]

If the ID is always the last 7 digits of the cell you could use the Left and Right functions to get the Name and ID

Name=left(cell,len(cell)-7)

id =right(cell,7)

 

[MikeIke]

If the ID is always the last 7 digits of the cell you could use the Left and Right functions to get the Name and ID

Name=left(cell,len(cell)-7)

id =right(cell,7)

Perfect! I couldn't figure out how to use Len in this situation. That should work just fine. Thanks!

 

[Buddy Ball 2K3]

just ran into this today...

I have a column of random numbers and letters in some cases there is an * in the number/letter string. Is there a way to just delete all of the *'s from the column? I am looking at having to go through 25K line items if not :(

 

[MikeIke]

just ran into this today...

I have a column of random numbers and letters in some cases there is an * in the number/letter string. Is there a way to just delete all of the *'s from the column? I am looking at having to go through 25K line items if not :(

Do a find-and-replace. Find * and replace with nothing.

 

[Buddy Ball 2K3]

just ran into this today...

I have a column of random numbers and letters in some cases there is an * in the number/letter string. Is there a way to just delete all of the *'s from the column? I am looking at having to go through 25K line items if not :(

Do a find-and-replace. Find * and replace with nothing.

Thank you, Tried this and I cant get it to work properly. It deletes everything in the columns. not sure what i am doing wrong.

 

[Ignoramus]

Select the entire column you want to clear the * from

Hit CTRL+H

In the find box, enter *

Leave the replace box empty

Hit the replace all button

 

[Patriotsfatboy1]

just ran into this today...

I have a column of random numbers and letters in some cases there is an * in the number/letter string. Is there a way to just delete all of the *'s from the column? I am looking at having to go through 25K line items if not :(

Do a find-and-replace. Find * and replace with nothing.

Thank you, Tried this and I cant get it to work properly. It deletes everything in the columns. not sure what i am doing wrong.

In the replace field, put in ~*

The tilde is a special character that denotes that the asterisk is a character and not a wildcard

 

[Ignoramus]

Oh wait, now I see the problem. * is a wildcard and stands for 'all'. Standby.

 

[Ignoramus]

just ran into this today...

I have a column of random numbers and letters in some cases there is an * in the number/letter string. Is there a way to just delete all of the *'s from the column? I am looking at having to go through 25K line items if not :(

Do a find-and-replace. Find * and replace with nothing.

Thank you, Tried this and I cant get it to work properly. It deletes everything in the columns. not sure what i am doing wrong.

In the replace field, put in ~*The tilde is a special character that denotes that the asterisk is a character and not a wildcard

 

[Sarnoff]

Oh wait, now I see the problem. * is a wildcard and stands for 'all'. Standby.

It was kind of fun watching this exchange play out. We've all been there.

 

[Buddy Ball 2K3]

Awesome!! Thank you gents. I would have never figured on the ~

 

[bushdocda]

Tilde!

 

[TheIronSheik]

Tilde!

Poop on the floor between the stalls.

 

[Nick Vermeil]

I would love to see an Excel thread of just useful, excellent formulas. Somebody get on that.

 

[TheIronSheik]

I would love to see an Excel thread of just useful, excellent formulas. Somebody get on that.

=IF(A12>B12,0,+$D$2*(NETWORKDAYS.INTL(A12,B12,Range1,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range1,Holidays)=0,MAX($B$2,$C$2),MAX($B$2,MIN(MOD(B12,1),$C$2)))
-IF(NETWORKDAYS.INTL(A12,A12,Range1,Holidays)=0,MIN($B$2,$C$2),MIN($C$2,MAX(MOD(A12,1),$B$2)))
+$D$3*(NETWORKDAYS.INTL(A12,B12,Range2,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range2,Holidays)=0,MAX($B$3,$C$3),MAX($B$3,MIN(MOD(B12,1),$C$3)))
-IF(NETWORKDAYS.INTL(A12,A12,Range2,Holidays)=0,MIN($B$3,$C$3),MIN($C$3,MAX(MOD(A12,1),$B$3)))
+$D$4*(NETWORKDAYS.INTL(A12,B12,Range3,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range3,Holidays)=0,MAX($B$4,$C$4),MAX($B$4,MIN(MOD(B12,1),$C$4)))
-IF(NETWORKDAYS.INTL(A12,A12,Range3,Holidays)=0,MIN($B$4,$C$4),MIN($C$4,MAX(MOD(A12,1),$B$4)))
+$D$5*(NETWORKDAYS.INTL(A12,B12,Range4,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range4,Holidays)=0,MAX($B$5,$C$5),MAX($B$5,MIN(MOD(B12,1),$C$5)))
-IF(NETWORKDAYS.INTL(A12,A12,Range4,Holidays)=0,MIN($B$5,$C$5),MIN($C$5,MAX(MOD(A12,1),$B$5))))

 

[Long Ball Larry]

just ran into this today...

I have a column of random numbers and letters in some cases there is an * in the number/letter string. Is there a way to just delete all of the *'s from the column? I am looking at having to go through 25K line items if not :(

Do a find-and-replace. Find * and replace with nothing.

Thank you, Tried this and I cant get it to work properly. It deletes everything in the columns. not sure what i am doing wrong.

In the replace field, put in ~*

The tilde is a special character that denotes that the asterisk is a character and not a wildcard

that's awesome, never know how to get around this problem.

 

[Nick Vermeil]

I would love to see an Excel thread of just useful, excellent formulas. Somebody get on that.

=IF(A12>B12,0,+$D$2*(NETWORKDAYS.INTL(A12,B12,Range1,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range1,Holidays)=0,MAX($B$2,$C$2),MAX($B$2,MIN(MOD(B12,1),$C$2)))
-IF(NETWORKDAYS.INTL(A12,A12,Range1,Holidays)=0,MIN($B$2,$C$2),MIN($C$2,MAX(MOD(A12,1),$B$2)))
+$D$3*(NETWORKDAYS.INTL(A12,B12,Range2,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range2,Holidays)=0,MAX($B$3,$C$3),MAX($B$3,MIN(MOD(B12,1),$C$3)))
-IF(NETWORKDAYS.INTL(A12,A12,Range2,Holidays)=0,MIN($B$3,$C$3),MIN($C$3,MAX(MOD(A12,1),$B$3)))
+$D$4*(NETWORKDAYS.INTL(A12,B12,Range3,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range3,Holidays)=0,MAX($B$4,$C$4),MAX($B$4,MIN(MOD(B12,1),$C$4)))
-IF(NETWORKDAYS.INTL(A12,A12,Range3,Holidays)=0,MIN($B$4,$C$4),MIN($C$4,MAX(MOD(A12,1),$B$4)))
+$D$5*(NETWORKDAYS.INTL(A12,B12,Range4,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range4,Holidays)=0,MAX($B$5,$C$5),MAX($B$5,MIN(MOD(B12,1),$C$5)))
-IF(NETWORKDAYS.INTL(A12,A12,Range4,Holidays)=0,MIN($B$5,$C$5),MIN($C$5,MAX(MOD(A12,1),$B$5))))

Holy crap. Please include explanation.

 

[ummich10]

SUMIFS is a great function in the newer versions of Excel. Same concept as SUMIF, but allows multiple summing criteria.

 

[TheIronSheik]

I would love to see an Excel thread of just useful, excellent formulas. Somebody get on that.

=IF(A12>B12,0,+$D$2*(NETWORKDAYS.INTL(A12,B12,Range1,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range1,Holidays)=0,MAX($B$2,$C$2),MAX($B$2,MIN(MOD(B12,1),$C$2)))
-IF(NETWORKDAYS.INTL(A12,A12,Range1,Holidays)=0,MIN($B$2,$C$2),MIN($C$2,MAX(MOD(A12,1),$B$2)))
+$D$3*(NETWORKDAYS.INTL(A12,B12,Range2,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range2,Holidays)=0,MAX($B$3,$C$3),MAX($B$3,MIN(MOD(B12,1),$C$3)))
-IF(NETWORKDAYS.INTL(A12,A12,Range2,Holidays)=0,MIN($B$3,$C$3),MIN($C$3,MAX(MOD(A12,1),$B$3)))
+$D$4*(NETWORKDAYS.INTL(A12,B12,Range3,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range3,Holidays)=0,MAX($B$4,$C$4),MAX($B$4,MIN(MOD(B12,1),$C$4)))
-IF(NETWORKDAYS.INTL(A12,A12,Range3,Holidays)=0,MIN($B$4,$C$4),MIN($C$4,MAX(MOD(A12,1),$B$4)))
+$D$5*(NETWORKDAYS.INTL(A12,B12,Range4,Holidays)-1)
+IF(NETWORKDAYS.INTL(B12,B12,Range4,Holidays)=0,MAX($B$5,$C$5),MAX($B$5,MIN(MOD(B12,1),$C$5)))
-IF(NETWORKDAYS.INTL(A12,A12,Range4,Holidays)=0,MIN($B$5,$C$5),MIN($C$5,MAX(MOD(A12,1),$B$5))))

Holy crap. Please include explanation.

It's just one cell of a spreadsheet I have that lets me enter in a time period and returns the time that actually occurred during store hours. The thing is a beast. I'm sure it could have been done easier, but it works, and that's all I care about.

 

[Ned]

SUBSTITUTE is an awesome formula when you need to manipulate data when you don't want to do a global find/replace.

 

[Ignoratio Elenchi]

I would love to see an Excel thread of just useful, excellent formulas. Somebody get on that.

http://forums.footballguys.com/forum/index.php?showtopic=678139

 

[Spin]

Index + Match combo is what I use most often. Much more versatile then vlookups.